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 would like to do is take every cell from the range A1:G10000 and if they are filled in with this color, I would like to have them turn bold, italics, red, and have the cell's fill color to turn to white. I have playing around with the code and so far have found that to this code will make the font red, bold and italics:

Selection.Font.Bold = True

Selection.Font.Italic = True

With Selection.Font

.Color = -16776961

.TintAndShade = 0

However, I have tried using parts of the code from other discussions to make this work in the IF function, and have not yet been able to. I would greatly appreciate if anyone is able to help me write this code. Thank you so much 🙂

excel

Excel on Mac

Formulas and Functions

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.

4 Replies

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!

Riny_van_Eekelen

Platinum Contributor

Feb 03, 2020

sirtajsingh

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.

sirtajsingh
Copper Contributor
Feb 03, 2020
Riny_van_Eekelen Thank you so much, that worked perfectly 🙂

Forum Discussion

Excel Macros If Function Based on Cell Color

4 Replies

Resources