Conditional Formatting, I think

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

Hello,

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.
Mo

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) &LT;&GT; ""
' 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

Loop

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.