Conditional Formatting cells in a large grid....

Hi

I have the attached spreadsheet which makes the question easier to understand. Basically when you read across and down rows and columns, I'd like the intersecting cell to turn red if the horizontal and vertical row columns letters are different. So in the attached B5 is red because B1=A and A5 is B. Where the intersecting cells match in values, the cells will stay white.

I can create a formula to conditionally format one cell to red: =A2<>B1

but i have a large grid and it would mean re-creating each formula for each cell. Any idea on how to expand it to the whole range?

Try

=$A2<>B$1

cf is formula option of =$A2<>B$1
applied to b2 and copied to rest of cells

Edit: How do I stop it from turning the cells a colour if only one of them has a value in it?

Perhaps..

=AND($A2<>B$1,$A2<>"")

........................

An easy way is to put a separate condition for this...

=b2="n"

I can't get this working as a conditional format, but the below obviously wont work. If one cell is more or less than another insert "n", otherwise leave blank.

=IF(A1<>B1,"n","")

Then format the cell with the chosen colour. What am i doing wrong?

See what i mean

Ok. Let's try in this way.

you cant insert text into a cell with cf,
as long as you dont type into those cells you can just put the same formula as the cf in the cell with an IF
if(AND($A2<>B$1,$A2<>""),"n","")

Originally Posted by Barking_Mad

bugger hmmmm..........

This means that no one of our 2 suggestions works for you?

Perhaps it's time to try to be more clear about the expected results? As you see both of us try to help you and i believe that both of us have the knowledge to do this..

If my understanding is correct, this should do what you want.