Change cell color

[dlorenc]

Based on the text in a cell (type in a b,g,y,o,r), I want to change the fill color. The following code kinna works...the problem is the interior color does not change on entry....it takes moving the back to the cell and hitting another enter to 'activate?' the color change....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 11 Then
If Target = "b" Or Target = "B" Then
Target.Interior.ColorIndex = 5
Target.Value = "B"
ElseIf Target = "g" Or Target = "G" Then
Target.Interior.ColorIndex = 50
Target.Value = "G"
ElseIf Target = "y" Or Target = "Y" Then
Target.Interior.ColorIndex = 36
Target.Value = "Y"
ElseIf Target = "o" Or Target = "O" Then
Target.Interior.ColorIndex = 44
Target.Value = "O"
ElseIf Target = "r" Or Target = "R" Then
Target.Interior.ColorIndex = 3
Target.Value = "R"
End If

End If

End Sub

 

[jdc]

Hi

Change your first line from Worksheet_SelectionChange to Worksheet_Change

Regards

John

 

[dlorenc]

ohhh...your good....*smile*

 

[Greg Truby]

Just some thoughts on the coding...

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column <> 11 Or .Count > 1 Then Exit Sub
        .Interior.ColorIndex = Choose(InStr(1, "BGYOR", UCase(.Value)) + 1, _
                               xlColorIndexNone, 5, 50, 36, 44, 3)
        ' Remove this enclosing IF/ENDIF if you always want
        ' values in column 11 to be converted to uppercase.
        ' Then move in front of the three interior lines of code
        ' before the .ColorIndex assignment line above and then
        ' you can take out the UCase() inside the InStr().
        If .Interior.ColorIndex <> xlColorIndexNone Then
            ' Set EnableEvents to False to avoid looping back
            ' into event handler [we're CHANGING a value while
            ' we're in the CHANGE EVENT handler].
            Application.EnableEvents = False
            Target = UCase(Target)
            Application.EnableEvents = True
        End If
    End With
End Sub

 

[dlorenc]

colors not carried into pivot table

ok....so the status colors are great in the database, I do a pivot table on that database, but the colors of the cells are not carried into the pivot table.

How do I get a status cell color from a range to come into the pivot table?

ie. I have a G (Green) cell in my data. I write the pivot table and it's G but with no fill.

 

[Greg Truby]

I'm not sure how the letters are turning up inside a PivotTable, but okey-doke... Something along the lines of the following ought to get you started:

Sub PaintThePivot()
    Dim rngCell As Range
    
    For Each rngCell In ActiveSheet.PivotTables(1).DataBodyRange.Cells
        With rngCell
            .Interior.ColorIndex = Choose(InStr(1, "BGYOR", UCase(.Value)) + 1, _
                                   xlColorIndexNone, 5, 50, 36, 44, 3)
        End With
    Next rngCell
End Sub

 

[dlorenc]

color letters are entered

not everyone has color printouts, or colorblind...so I use the letters as the primary data entry, change the cell color on a change event (for those that can use the colors)..now I want the color to carry over into the pivot...

I'll try your solution...