Change cell color

dlorenc

Board Regular
Joined
Aug 17, 2004
Messages
140
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jdc

Active Member
Joined
Nov 23, 2004
Messages
263
Hi

Change your first line from Worksheet_SelectionChange to Worksheet_Change

Regards

John
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Just some thoughts on the coding...
Code:
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

Board Regular
Joined
Aug 17, 2004
Messages
140

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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:
Code:
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

Board Regular
Joined
Aug 17, 2004
Messages
140
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top