Best method for automatically changing an entire row's fill/font color based on one cell

[dtarockoff]

I have a master list of contact information that I color code by hand depending on a designation I give each row. The designation is just a single letter, "X" for example, and is listed under it's own column within each row.

In the example below, I'd want rows (not just the cells A1 and A3) 1 and 3 to have the same font color and fill color, which would automatically be adjusted based on the letter typed into column A. Row 2 would have a different font color and fill color but would also automatically be adjusted based on the "B" typed in A2.
-----A-----B-----C
1 X 1 2
2 B 1 2
3 X 1 2

Previously, I successfully did this with conditional formatting, by selecting the entire sheet and using a variation of the formula =COUNTIF($A1,"X"). I would use the "highlight cells" conditional formatting setting and just use a custom version of font colors and fills each time I made a new 'rule'. This has worked fine, but for some reason it won't work now that I've moved the data to a new sheet and reapplied the same rules.

Regardless, I think this is probably not the easiest nor most efficient way of doing this so I was wondering if anyone had any other ideas that didn't involve conditional formatting. Or, a better formula than the one I'm using in conditional formatting, maybe.

Thank you!

 

[mumps]

Place this code in the code module for your worksheet (not into a regular module). You can add additional colors simply by adding more 'ElseIf' statements. Please let me know if this works out for you.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    If Target = "X" Then
        Target.EntireRow.Interior.ColorIndex = 3
    ElseIf Target = "B" Then
        Target.EntireRow.Interior.ColorIndex = 6
    End If
End Sub

 

[dtarockoff]

I appreciate the help, but I'm trying to avoid having to use VBA. Besides the fact that I'm a beginner when it comes to VBA, I have very specific colors I need to use that I don't know the "codes" for and I have over 10 letter combinations to assign colors to.

 

[mumps]

If you send me the colors along with the letters associated with them and you are willing to give VBA a try, I'll be happy to update the macro for you. After that is done, all you have to do is the following:
-right click the worksheet tab
-click 'View Code'
-copy and paste the code into the empty code window that opens up
-close the code window
Now when you enter a letter in column A and exit the cell, that entire row will be colored automaticaly. That's it!

 

[dtarockoff]

Problem solved! Thanks

 

[mumps]

For those interested, this is the code:

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Select Case Left(Target, 1)
    Case "A"
        Target.EntireRow.Interior.Color = 9737946
    Case "D"
        Target.EntireRow.Interior.Color = 5287936
        Target.EntireRow.Font.Color = vbWhite
    Case "H"
        Target.EntireRow.Interior.Color = 11851260
    Case "I"
        Target.EntireRow.Interior.Color = 14994616
    Case "M"
        Target.EntireRow.Interior.Color = 49407
    Case "O"
        Target.EntireRow.Interior.Color = 10213316
    Case "P"
        Target.EntireRow.Interior.Color = 65535
    Case "S"
        Target.EntireRow.Interior.Color = 255
    Case "T"
        Target.EntireRow.Interior.Color = 10498160
        Target.EntireRow.Font.Color = vbWhite
    Case "U"
        Target.EntireRow.Interior.Color = 2704713
        Target.EntireRow.Font.Color = vbWhite
    Case "G"
        Target.EntireRow.Interior.Color = 16777215
        Target.EntireRow.Font.Color = vbWhite
    End Select
End Sub

 

[dtarockoff]

Thanks again mumps!

 

[lalord69]

thanks for the code!

 

[mumps]

My pleasure.