Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

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 :)
0
cegeland
Asked:
cegeland
  • 3
  • 3
  • 2
1 Solution
 
Rahul_GadeCommented:
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
 
cegelandAuthor Commented:
And finally - if the value in Column 3 is NOT equal to "Group A" or "Group B" then clear formatting.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rahul_GadeCommented:
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
 
Rahul_GadeCommented:
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now