Conditional Formatting linked to value in a cell

[Mark McInerney]

Hi All,

I have a range of data a1:d4 (4 rows x 4 columns range). I also have a range of data in column e1:e4. If I populate E1 with the value 2, I want the second cell in the range a1:d1 to be formatted with a green fill (B1).

If I populate E4 with the number 3, I want the third cell over in the row to be formatted with a green fill (C3).

If there are no values in the e1:e4 range, then there is no conditional formatting required.

Any help appreciated.

 

[cerfani]

Select cell B1 then select conditional formatting in the ribbon area (under Home I think), select the option to use a true/false formula to determine format. Then put =E1=2 ... and under the format to apply pick the color to fill. Same idea for setting E4.

 

[Mark McInerney]

My dataset is a lot bigger, I cut it down for the purposes of the request - does your post mean that I would have to select every cell individually?

BTW - Thanks for taking time to review this.

 

[cerfani]

No prob, and no you just select the entire range that needs to be formatted and the formula you write is based on the first cell in the range... conditional formatting will know to adjust the formula for each cell in the selection as if you had dragged the formula.

Like if your criteria formula was =$E$1=2 then for every cell selected it will only look at E1 but if you remove the dollar signs, then the second cell in the conditionally formatted range will format based on E2 ... =E2=2 ...assuming the range is a single column going down.

 

[Mark McInerney]

Hi - Got it! Thanks for guidance.

Mark.