Change colour of cell based on the value of another cell VBA

[tanmigd]

Hey,
I'm having difficulty trying to figure this out. I want to basically make cases in column (say column K) and make another column (say column O) display color.
So for example, if I write down in Column K the word "Red", I would want the color red to be displayed in column O. If i write "Blue" in column K, I want the color blue to be displayed in column O. Colors would be repeated, so I don't need infinite colors. My range is from 1:100. I've done this via conditional formatting, but I want to do this via VBA.
Any solution or advice, would be appreciated.
Thank you!

 

[Scott T]

When do you want the color to display as entered only when you run the code?

 

[JoeMo]

This is sheet code that will run automatically as entries are mad to col K.
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Columns("K")) Is Nothing Then
    
    For Each c In Intersect(Target, Columns("K"))
        'case sensitive
        Select Case c.Value
            Case "Red": c.Offset(0, 4).Interior.Color = vbRed
            Case "Yellow": c.Offset(0, 4).Interior.Color = vbYellow
            Case "Green": c.Offset(0, 4).Interior.Color = vbGreen
            Case "Blue": c.Offset(0, 4).Interior.Color = vbBlue
            'add other colors to suit
            Case Else: c.Offset(0, 4).Interior.Color = xlNone
        End Select
    Next c
End If
End Sub

 

[tanmigd]

This is sheet code that will run automatically as entries are mad to col K.
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Columns("K")) Is Nothing Then
   
    For Each c In Intersect(Target, Columns("K"))
        'case sensitive
        Select Case c.Value
            Case "Red": c.Offset(0, 4).Interior.Color = vbRed
            Case "Yellow": c.Offset(0, 4).Interior.Color = vbYellow
            Case "Green": c.Offset(0, 4).Interior.Color = vbGreen
            Case "Blue": c.Offset(0, 4).Interior.Color = vbBlue
            'add other colors to suit
            Case Else: c.Offset(0, 4).Interior.Color = xlNone
        End Select
    Next c
End If
End Sub

It didn't work display any color when I typed in the colors in the K column. Do you have any other suggestion?

 

[Scott T]

As noted in the code it is case sensitive so when you type in red it has to match the code Red.
if this is a problem you can list all the colors in uppercase and have the code make the value upper case.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Columns("K")) Is Nothing Then
    
    For Each c In Intersect(Target, Columns("K"))
        
        Select Case UCase(c.Value)
            Case "RED": c.Offset(0, 4).Interior.Color = vbRed
            Case "YELLOW": c.Offset(0, 4).Interior.Color = vbYellow
            Case "GREEN": c.Offset(0, 4).Interior.Color = vbGreen
            Case "BLUE": c.Offset(0, 4).Interior.Color = vbBlue
            'add other colors to suit
            Case Else: c.Offset(0, 4).Interior.Color = xlNone
        End Select
    Next c
End If
End Sub

 

[JoeMo]

It didn't work display any color when I typed in the colors in the K column. Do you have any other suggestion?

I suggest you go back to the instructions for installing the code to be certain you have followed them correctly, being sure to copy the code from your browser rather than typing it in. If that doesn't work be sure you are using all caps when you type in the colors. If still not working, be sure that events are enabled following the steps below:
1. with your workbook open press Alt + F11 to open the VBE.
2. Press ctrl + g to open the Immediate Window.
3. In the Immediate Window type: "Application.EnableEvents = True" w/o the quote marks and press Enter
4. Go back and enter a color (RED, YELLOW, GREEN or BLUE) in col K and see if col O corresponding cell fills wit that color.

 

[tanmigd]

I suggest you go back to the instructions for installing the code to be certain you have followed them correctly, being sure to copy the code from your browser rather than typing it in. If that doesn't work be sure you are using all caps when you type in the colors. If still not working, be sure that events are enabled following the steps below:
1. with your workbook open press Alt + F11 to open the VBE.
2. Press ctrl + g to open the Immediate Window.
3. In the Immediate Window type: "Application.EnableEvents = True" w/o the quote marks and press Enter
4. Go back and enter a color (RED, YELLOW, GREEN or BLUE) in col K and see if col O corresponding cell fills wit that color.

It worked!!!!! Thank you

 

[JoeMo]

You are welcome - thanks for the reply.