MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional Formatting, I think

Posted by Mo on June 05, 2001 1:20 PM


I’ve got a problem that I hope someone will be able to solve.
Assume 2 columns, A & B with lots of entries in them. Now assume we are in column A5. IF Cell A5 is equal to A6, and correspondingly cell B5 is equal to B6. I would like all 4 cells to be formatted to a colour.
So E.g Cell A5=10 and cell A6=10, at the same time Cell B5 & B6 have the entry “apple”. Then A5 to B6 should be yellow.
Please remember this process needs to happen many times as there are many entries like these.
I will not mind if a different colour is used whenever this happens e.g first instance yellow, second instance red etc.
If anyone can solve this it will be very much appreciated, macro’s or formulas kindly accepted.

Posted by Russell on June 05, 2001 1:45 PM

I would do something like this:

intRow = 1
intCol = 1

Do While Trim(Cells(intRow, intCol).Text) <> ""
' The above assumes that your data is continuous;
' that there are no blank rows until the bottom

if Cells(intRow, intCol) = Cells(intRow + 1, intCol) AND Cells(intRow, intCol+1) = Cells(intRow + 1, intCol + 1) then

Range(Cells(intRow, intCol), Cells(intRow + 1, intCol + 1).Font.ColorIndex = 3 '(that's red)

'or, if you want to change the background color:

Range(Cells(intRow, intCol), Cells(intRow + 1, intCol + 1).Interior.ColorIndex = 36 '(that's light yellow fill)

introw = introw + 1


Posted by Chuck on June 05, 2001 2:02 PM

Formula: =OR(AND(A5=A6,B5=B6),AND(A5=A4,B5=B4))

Enter Conditional format formula in both columns and choose whatever color you like.

Posted by Mo on June 05, 2001 4:09 PM

Thankyou both very much.