CODEWITHSUNDEEP
excelconditional-formatting
tip2tail
tip2tail

Reputation: 463

Conditional format Excel multiple adjacent cells in range

I have an Excel spreadsheet where data in is in the range B4:K12. Each row has 5 sections (2 columns each).

I need to format B4 and C4 identically based on if C4 = "D". Then D4 & E4 if E4="D" etc throughout the range.

I have not been able to get anything to work here. How should this be done?

Thanks, t2t

Upvotes: 0

Views: 63

Answers (1)

Ditto
Ditto

Reputation: 3344

Try this formula:

=OFFSET($B4,0,((INT((COLUMN(B4)/2)-1)*2)+1),1,1)="D"

Does that do what you want ?

Basically this is what it's doing:

OFFSET($B4,....) is taking the first column of each row as it's reference point. We then just need to figure out which column we need to look at.

COLUMN(B4) - take the column # of the cell in question.

((INT((COLUMN(B4)/2)-1)*2+1) is just doing adjustments to the column number to turn it into an offset.

Try running just this part of the formula in a cell on row 20 .. build it up and you'll see it convering :2, 3, 4, 5, 6 .... into 1,1,3,3,5, ... etc.

Upvotes: 1

Related Questions