Macro for conditional formatting (cell fill color)

Hi!

I'm looking for a macro that can change the cell fill color according to a value in an adjacent column every time the cell is changed.

I have a Column A which contains values (calculated by formula). In Column B i have sorted the rows into groups (Group A, Group B, Group C etc). I would like to have the fill color in Column A change according to the value in Column B everytime the value is changed in Column A. Typically Group A gives a green cell fill, Group B a red fill and so on...

I cannot use the conditional formatting functionality in Excel 2010 due to the limitations when the file is opened in Excel 2003.

All help greatly appreciated :)
cegelandAsked:
Who is Participating?
 
Rahul GadeConnect With a Mentor Sr. ArchitectCommented:
You can make use of following script but will need to be modified for number of rows identification, i have assumed it to take 99 rows only as 1st row reserved for group values.

Private Sub Worksheet_Calculate()

For Row = 2 To 100 Step 1

    Dim target As Range
    Set target = Sheets(1).Range("C" & Row)

    'If target.Cells.Column = 3 Then
   
    Dim groupA As Integer
    Dim groupB As Integer
   
    'Just assuming your groupA and groupB are first 2 cells, replace with appropriate cell range
    groupA = target.Worksheet.Cells(1, 1).Value
    groupB = target.Worksheet.Cells(1, 2).Value
   
        Select Case target.Worksheet.Cells(target.Cells.Row, 3).Value
        Case groupA:
            target.Worksheet.Cells(target.Cells.Row, 2).Interior.Color = RGB(200, 0, 0)
        Case groupB:
            target.Worksheet.Cells(target.Cells.Row, 2).Interior.Color = RGB(0, 200, 0)
        Case Else:
            target.Worksheet.Cells(target.Cells.Row, 2).Interior.ColorIndex = 0
        End Select
    'End If
Next Row
End Sub

-Rahul
0
 
Rahul GadeSr. ArchitectCommented:
See if this helps :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Column = 2 Then
  Target.Worksheet.Cells(Target.Cells.Row, 3).Interior.Color = RGB(200, 1, 2)
End If
End Sub

-Rahul
0
 
cegelandAuthor Commented:
Thanks for your quick reply. This is in the right direction. Now every cell in column 3 becomes red when I change the value in Column 2.

I would like to have it the other way around - Column 2 changes color based on the value of Column 3.

Say if Column 3 equals "Group A" then give the cell in Column 2 a red fill. If Column 3 equals "Group B" then give Column 2 a green fill.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
cegelandAuthor Commented:
And finally - if the value in Column 3 is NOT equal to "Group A" or "Group B" then clear formatting.
0
 
Rahul GadeSr. ArchitectCommented:
I hope this solves your problem:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Column = 3 Then

Dim groupA As Integer
Dim groupB As Integer

'Just assuming your groupA and groupB are first 2 cells, replace with appropriate cell range
groupA = Target.Worksheet.Cells(1, 1).Value
groupB = Target.Worksheet.Cells(1, 2).Value

    Select Case Target.Worksheet.Cells(Target.Cells.Row, 3).Value
    Case groupA:
        Target.Worksheet.Cells(Target.Cells.Row, 2).Interior.Color = RGB(200, 0, 0)
    Case groupB:
        Target.Worksheet.Cells(Target.Cells.Row, 2).Interior.Color = RGB(0, 200, 0)
    Case Else:
        Target.Worksheet.Cells(Target.Cells.Row, 2).Interior.ColorIndex = 0
    End Select

End If
End Sub
0
 
cegelandAuthor Commented:
Thanks! This works perfectly when I manually enter Group A or Group B.

However I have a forumula in the cell that pulls this information from a different cell. I belive this does formula does not trigger a worksheet_change, so the cell fill is not updated.

Do you have a solution for this as well :) ?
0
 
candychan611Commented:
Rahul,

Hi i just  fresh to visual basic.
Sorry i try hard to study the  codes, seems still unknow how to apply.

Regards
Stanley
0
 
candychan611Commented:
Sorry Wrong message !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.