changing cell color depending on value 1

[shytott]

I'm trying to change the background color of a group of 3 cells (in a single row) depending on the text content entered in just one of the cells. The cells are arranged in table.

I've tried conditional formatting, but this only changed the color of one of the cells (the one with the value in it), not all 3. The other 2 cells have random data in them not relating to the value cell, but still need to be highlighted.

Any ideas?

 

[Zathras]

Use absolute addressing with "Formula is" - for example if cell B7 is the controlling cell, then higlight all three cells and enter this formula to test for a value less than 5 in the controlling cell.[tt]
=$B$7<5
[/tt]
and assign the formatting.

(Use your own formula once you understand the principle.)

 

[shytott]

Thanks for the quick response Zathras.
Im still having a problemette here....
This is what Im trying to do.....
Contents of 3 cells could be.....

OOS mini 1234567
DEF mini 2838485
LOR escort 3348769
OOS Lexus 3986756

OK, so what id like to happen is for the contents of the fisrt cell to identify a given color so in the above, both the 'OOS' lines would be eg red. There are a total of 6 unique colors for the fisrt cell identifiers.
When I tried using the Conditional formatting idea, the contents of the cell was chnaging to equal the identifier cell.

Hope this helps!
Cheers

 

[SgtJarrow]

I was wondering this myself and basically am gathering the following...

When using formula based conditional formatting, you can only evaluate something that returns a true or false, so thing like greater than (>), less than (<) etc. I could not find any reference that allows equals (=) to work.

I was able to work around this with the following....I created a hidden column that contained a number based on the text in the visible cell. These numbers were set somewhat apart. The I created a conditional format for this hidden cell that modified the visible cells....checking the hidden cell was < some number and greater than another, both of which defined my range.

Worked perfectly once I set it up and copied it down the page using the format painter. Thanks so much to Zathras to give me the nudge in the right direction and to find what I needed to search for to find the answer.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[AnotherHiggins]

NOTE: Conditional Formatting has a limit of three criteria. If you choose a background color for the cells that are not identified by any of those three criteria, you have a limit of four unique colors.

First, I'll address how to make the single cell control the formatting of all three via Conditional Formatting.

-In your example (assuming you don't have a header row), highlight all three columns (A, B and C).
-Now go to Format > Conditional Formatting.
-Change the first box to Formula Is.
-In the second box, type in [COLOR=blue white]=$A1="OOS"[/color].
-Press Format
-Go to the Patterns tab.
-Choose Red.
-Press OK
-Press OK

See Excel's help file for more info on Relative Reference vs. Absolute Reference.

As I said, you will be limited to four unique colors using Conditional Formatting.

In order to have six unique colors, you will need to use a macro (VBA). Do you want to pursue that option?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[shytott]

John,

Got that working fine, thanks for your help.

However, for the formatting to be completely usefull i would need to use more than 4 colors, so i would like to pursue the VBA option.

 

[AnotherHiggins]

It will look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Target
    Select Case myCell.Value
        Case Is = "OOS"
            Range(myCell, myCell.Offset(, 2)).Interior.ColorIndex = 3
        Case Is = "DEF"
            Range(myCell, myCell.Offset(, 2)).Interior.ColorIndex = 4
        Case Is = "LOR"
            Range(myCell, myCell.Offset(, 2)).Interior.ColorIndex = 5
        Case Is = "SomethingElse"
            Range(myCell, myCell.Offset(, 2)).Interior.ColorIndex = 7
        Case Else
            Range(myCell, myCell.Offset(, 2)).Interior.ColorIndex = xlNone
    End Select
Next myCell
End Sub

Right click on the sheet tab and select View Code. Copy 'n' Paste that code there.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.