How to conditional format each row of a column based on corresponding value in another column-CodePudding

Please refer the image I want the cell to be highlighted based on the value is less than or greater than the value in the benchmark column. I am not able to do that using conditional formatting custom formula. I have manually applied formatting for 02/01/2023 . I want the formatting to apply to the column with date = today() only. Thanks :)

spreadsheet screenshot

I can write a custom formula for each row of each date column. But is there any way a single custom formula that could format across rows and columns?

CodePudding user response：

I'm guessing your fist value of 02/01/2023 and Activity1 is C2. Then for the whole range C2:Z (or whichever you have):

=C2>=$B2

Do this for one color for the whole range and it will drag automatically, you don't need to write it as an arrayformula. The "$" will always refer to the value in column B from the row it's positioned

CodePudding user response：

if you are selecting whole range (C2:Z), try this for green and red respectively:

=(C2>=$B2)*(C2<>"")

=(C2<$B2)*(C2<>"")