Special Conditional Format

[MasterOfDisaster]

The title is not very descriptive, I realize. I need to highlight the whole row of a range based on its row number denoted in a given cell.

So for example, if my range is D5:K10 and the value in cell A1 is 3, I need to highlight the whole second row of the range (that is D7:K7). If A1 is 6 then range D10:K10 should be highlighted.

Either a conditional format or a macro solution would work, the preference is for the former.

Thanks in advance.

 

[Cbrine]

if you use absolute addressing($A$1) instead of relative(A1) in your conditional formating, you should get the effect you desire.

HTH
Cal

Using your example, he is your condition.

=$A$1=3

Assuming you want to do multiple rows use this.
=$A1=3

 

[MasterOfDisaster]

Where would I indicate the row number to highlight? The difficulty is that it is relative to the range that I'm using.

 

[Cbrine]

if you want highlight columns D to K, you would first click on D1(or your first conditional range cell), then highlight your entire table. Next set your conditional format as per my last example. Set you conditional highlight and press OK. Excel is smart enough to adjust the cell reference based on the highlighted area.

HTH
Cal

 

[dave3009]

Hi There

If the Row is important then you could try something like

=(ROW()-6)=$A$1

Given that you if A1 had 1 in it then row 7 would get highlighted

HTH


Dave

 

[MasterOfDisaster]

Ah yes, thank you very much. Had to use my head a little, appreciate that.

 

[MasterOfDisaster]

Thanks Dave, I was able to derive that.