Help with Excel Conditional Formatting

[TREMAiNE]

Hi Guys,

Need a bit of help with Conditional Formatting and you've all been very helpful in the past with my macro and formula queries.

I'll try and explain it as simply as possible.

I have 2 columns that will contain a location in each row. If the locations in the 2 columns on the same row match, the rest of that row will turn grey.
If the location for each column on the same row are different, the row reverts back to white.

I can set up the conditional formatting without problems:





However, I have over 200 rows to do this for, but I don't know how to 'follow it through' for each row.

I've tried 'dragging' the cells that contain the formatting down, in the same way normally do for formulas (and it works fine) like this:





But it doesn't work, whilst the formatting does carry across, it still bases the 'result' on the first row the formatting was based on, so as you can see below, The Moon and Jupiter don't match, yet the rest of the row is still grey - it's all tied to whether 'Colchester and Colchester' match.

If I change one of the Colchester's, all four of those example rows change from grey to white.



Hopefully, this makes sense.

How do I get this to follow through row by row?
I really cannot be bothered to do all 200+ rows manually.

Edited by TREMAiNE on Thursday 24th March 13:50

[toon10]

You need to use the format painter. Copy the cell with the conditional format and use the format painter (paintbrush icon) and drag that down to the other cells you want to replicate it to.

[DE1975]

remove the $ sign before the 39. This is fixing the referenced cells in the formula in line 39. You can then copy and paste formats down the page

[fbc]

TREMAiNE said:

...

But it doesn't work, whilst the formatting does carry across, it still bases the 'result' on the first row the formatting was based on, so as you can see below, The Moon and Jupiter don't match, yet the rest of the row is still grey - it's all tied to whether 'Colchester and Colchester' match.

Edited by TREMAiNE on Thursday 24th March 13:50

You've used (or Excel has defaulted to using) absolute cell references in the first row you formatted. Remove the second '$' from each cell reference - ie so your first row formula would =$B39=$C39, so that the row being referenced becomes relative, not absolute and tied to row 39.

[TREMAiNE]

I've wasted an hour and a half trying to figure it out myself...

PH comes along and sorts it in 5 minutes.

Thanks, guys, all sorted.