Using VBA instead of Conditional formatting

[QU4487]

Hi hopefully somebody can help. Using Conditional Formatting I've built a workbook which checks for a number in a series of columns on one worksheet and if it finds a it in a grid (named range 'Grid'), formats the cell to a particular colour. This works great, but I have 150 CFs so the whole workbook is very sluggish. What I would like it to replicate the CFs in VBA so that I can then choose when to run (ie it isn't volatile, so isn't processor heavy). My first attempt in VBA looks like this;

The Named range 'Grid' = A5:AX204

Sub Macro1()
'
' Macro1 Macro
'

'
Application.Goto Reference:="Grid"
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(MATCH(A5,WDC!$A:$A,0))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(MATCH(A5,WDC!$C:$C, 0))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

Unfortunately this just inserts the original CFs rather than allowing me to choose when to run. I'm terrible with VBA, so hoping that this is an easy-ish answer for somebody.

Any help is greatly appreciated.

 

[QU4487]

Hello, i think this article can enlighten you about it.

How to convert color codes (RGB, HSL, HSV, Hex, Long, CMYK)

Custom VBA functions to convert color codes - RGB, HSL, HSV, Hex, Long and CMYK). Includes Free Excel color converter add-in

exceloffthegrid.com

They are essentially the same thing, it's just a more "direct" reprensentation of the RGB value

Short answer :
Long = Blue x 256 x 256 + Green x 256 + Red

That makes sense, and saves some faff in VBA.

Cheers

 

[mumps]

This version will change the font to white:

Sub ColorCells()
    Application.ScreenUpdating = False
    Dim v As Variant, v2 As Variant, v3 As Variant, r As Long, c As Long, lRow As Long
    With Sheets("PIDs")
        v = .Range("A5").CurrentRegion.Value
        .Range("A5", .Range("AX" & .Rows.Count).End(xlUp)).Interior.Color = xlNone
    End With
    With Sheets("WDC")
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        v2 = .Range("A2:A" & lRow).Value
        v3 = .Range("B2:B" & lRow).Value
    End With
    For r = LBound(v) To UBound(v)
        For c = LBound(v, 2) To UBound(v, 2)
            If Not IsError(Application.Match(v(r, c), v2, 0)) Then
                Sheets("PIDs").Cells(r + 4, c).Interior.Color = 36799
            ElseIf Not IsError(Application.Match(v(r, c), v3, 0)) Then
                With Sheets("PIDs").Cells(r + 4, c)
                    .Interior.Color = 3243501
                    .Font.Color = vbWhite
                End With
            End If
        Next c
    Next r
    Application.ScreenUpdating = True
End Sub

I got the color from the conditional formatting that you had in place to make sure that the macro used the same colors. If you are flexible with which colors you use, that can be changed to use colorindex or RGB. What is the significance of the colors you have at the top of your sheet?

 

[Micron]

I think there is an error on that page?

"The first two characters represent blue, the middle two characters represent green and last two are blue."