Changing Cell Colors Based on Other Cell Values

**Vinx** · 10-08-2010, 10:17 PM

Hello guys,

As the title states, I'm currently trying to change cell colors on a Main sheet based on whether or not a value is present on another sheet. There are 6 other sheets total, and only one value can be found on any one cell of any sheet at a time.

So for example, if B7 on sheet 3 = 5.5, then B7 on every other sheet will be completely blank.

Each sheet has its own color. I want the main sheet's cell color to correspond whichever sheet's cell whose value is > 0. So in the example above, if sheet 3 was green, I'd want the main sheet's B7 cell to be green.

I consider myself an experienced visual basic programmer but I just don't know the syntax very well (or at all, really) for excel in VB. Also, it may even be doable with just the excel functions, but once again I'm not very versed in excel syntax. Also, I'm having a hard time referencing cells on other sheets to begin with.

If anyone can help me with this issue, I'd be extremely grateful. Thanks in advance for any support!

- Vinx

**Leith Ross** · 10-08-2010, 10:34 PM

Hello Vinx,

Welcome to the Forum!

Posting a workbook is the fastest way to get answers to your questions and assures they will be correct.

**foxguy** · 10-10-2010, 10:47 AM

To see the syntax for referring to cells on other sheets:

1) Pick a cell on main sheet (say C5)
2) Type "=" (without the quotation marks)
3) Use your mouse to select tab of different sheet (you should see the tab's name in the formula bar now)
4) Use your mouse to point to the cell on this tab that you want to check value of
5) Type ">0" (without the quotation marks)
6) Click <Enter> (This will put a "True" or "False" in cell C5).
7) put your cursor in the formula bar in front of the "=" and put a "'" in front of it.

Now you can see the syntax in cell C5 for checking to see if a cell on a different sheet > 0 and you can use that syntax inside Conditional Formatting to change the color of a cell on the main sheet.

**whatsmyname** · 12-10-2010, 07:02 PM

Hello Foxguy,

I tried this just because I am trying to do something similar, except I need to compare the 2 cells and highlight if they match.

But, besides that, I got an error when I pasted in the formula under conditional formatting. It said that you cannot reference another sheet when you are doing conditional formatting. so I deleted the sheet reference and just had it associate it with another cell on the same sheet and it worked flaelesssly. I don't know if I did something wrong or not.

**foxguy** · 12-10-2010, 07:10 PM

I never noticed that you can't reference another sheet in conditional formatting before, but I got the same error when I tried to do it. I had never tried it before.

So it doesn't sound like you did anything wrong.