Excel Macros If Function Based on Cell Color

I don't know how to code in VBA but am trying to automate an if/then calculation based on cell color. So I have many cells that are filled in with the RGB color code RGB (255, 235, 156). What I w...

excel

Excel on Mac

Formulas and Functions

Feb 03, 2020

Try this one:

Sub ColorMacro()

Dim myCell As Variant

For Each myCell In Range("A1:G10000")

    If myCell.Interior.Color = 10284031 Then
    
        myCell.Font.Bold = True
        myCell.Font.Italic = True
        With myCell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 16777215
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        
        With myCell.Font
            .Color = -16776961
            .TintAndShade = 0
        End With
        
    End If
    
Next myCell

End Sub

The color value of your RGB values = 10284031 (i.e. the result of R+G*256+B*256*256). You need this to be able to read out color value in each of your cells.

PeterBartholomew1

Silver Contributor

Feb 03, 2020

sirtajsingh

Find and Replace will also work, either manually or from VBA

Sub ChangeFormat()
    With Application.FindFormat.Interior
        .Color = RGB(255, 235, 156)
    End With
    With Application.ReplaceFormat
        .Interior.Color = RGB(255, 255, 255)
        With .Font
            .Color = RGB(255, 0, 0)
            .Bold = True
            .Italic = True
        End With
    End With
    Range("SearchRange").Replace What:="", Replacement:="", _
    SearchFormat:=True, ReplaceFormat:=True, _
    FormulaVersion:=xlReplaceFormula2
End Sub

Note: 'SearchRange' is a range name that I defined from the user interface.

sirtajsingh

Copper Contributor

Feb 03, 2020

PeterBartholomew1 I tried this as well and it worked perfectly! I never even thought of using the find and replace to change the formatting, thank you!

Forum Discussion

Excel Macros If Function Based on Cell Color

Resources