# Conditional Formatting cells in a large grid....

1. ## 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

3. ## Re: Conditional Formatting cells in a large grid....

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

4. ## Re: Conditional Formatting cells in a large grid....

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

5. ## Re: Conditional Formatting cells in a large grid....

Perhaps..

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

6. ## Re: Conditional Formatting cells in a large grid....

Originally Posted by Fotis1991
Perhaps..

=AND(\$A2<>B\$1,\$A2<>"")
Thanks that works a treat. Cant get my head round equations. When someone types them they makes sense, when i try to make them, it all goes pear shaped!

7. ## Re: Conditional Formatting cells in a large grid....

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

8. ## Re: Conditional Formatting cells in a large grid....

Ah, just noticed one more thing. I have another equation which before the conditional formatting counted the number of times "n" was in a cell. Can:

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

be adjusted to it also places "n" inside a cell that is turned the colour of the conditional formatting?

9. ## Re: Conditional Formatting cells in a large grid....

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

=b2="n"

10. ## Re: Conditional Formatting cells in a large grid....

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?

11. ## Re: Conditional Formatting cells in a large grid....

See what i mean

12. ## Re: Conditional Formatting cells in a large grid....

Hi,

Thanks again for the swift reply

I don't think im explaining myself very well. You kindly sorted it out so that the cells turned green in your example sheet. Perfect. They also need to have "n" inserted at the same time they turn green. So all those cells in the example sheet that are green, would also have the letter "n" in them. This is so I can do a calculation based on the number of times "n" features in the grid.

And in case you're wondering! The purposes of it turning green automatically was so that the user wouldn't have to enter "n" every time the cells weren't identical - as it's a large sheet and quite time consuming...

Thanks and apologies

13. ## Re: Conditional Formatting cells in a large grid....

Ok. Let's try in this way.

14. ## Re: Conditional Formatting cells in a large grid....

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","")

15. ## Re: Conditional Formatting cells in a large grid....

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

16. ## Re: Conditional Formatting cells in a large grid....

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

17. ## Re: Conditional Formatting cells in a large grid....

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

18. ## Re: Conditional Formatting cells in a large grid....

Hi,

THanks for your help, I didnt want a formula in unfortunately, as I have other values which would be potentially messed up if they were deleted. I went on the macro thread and got a solution. NOt perfect but it works well

Thanks all for your time, much appreciated.

http://www.excelforum.com/excel-prog...in-a-grid.html

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1