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.

 

[Micron]

I don't think replicating cf rules with vba will speed things up. What might is using Find method over your range and setting the background colour for found cells. However, this isn't conditional, so you would need to decide on what is going to trigger the code, such as events like Change or double/right clicks or Activate or whatever. If you chose Because it's not conditional, editing the cell will not change the colour when it no longer contains the value. To get around that, I'd set the entire sheet to be the normal colour (or just a range if you have coloured headers and such), then find the cells with the value and colour those.

Note that I'm more adept with Access VBA. I manage to provide the odd code solution but it usually takes me a while because Excel VBA seems more complicated. I have code that uses .Find but can't even tell you if it will find all cells in a range with a value. I'd have to test it, assuming you'd even want to use the aforementioned approach.

Please post code within code tags (use VBA button on forum posting toolbar) to maintain indentation and readability. Wish I had a nickel for every time I've written that here and elsewhere!

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[QU4487]

Sorry for the slow reply. Thanks for coming back so quick

Here is a link to a stripped down copy of the Workbook. I've taken out my Johnnie aged five macro, as it was just replicating the CF. I'm looking for a Macro which will format the cells in the same way as the CF on the attached sheet, but
only runs when I want it to. So I'm guessing that the first part of the macro resets every cell back to clear each time.

https://www.dropbox.com/scl/fi/rx4r9g6151onbtdq8k17d/ConditionalFormat.xlsx?rlkey=rtsk1djubnxcnzmmbggv9a8rs&st=g2v9jg7z&dl=0

 

[saboh12617]

Hi,
Sorry i just arrive. I have some difficulties understanding which 150 CF you are referring to. On the given sheet there is only 2 CF as far as i know.
As mentioned above, i would agree on the idea that the best way to "fake" CF is using the function Find, which is very fast. But you need to tell us which format to apply and following which rules?
Thank you

 

[mumps]

Remove the conditional formatting and try:

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
                Sheets("PIDs").Cells(r + 4, c).Interior.Color = 3243501
            End If
        Next c
    Next r
    Application.ScreenUpdating = True
End Sub

 

[QU4487]

That's exactly the thing I was looking for. Thank you so much. Now if I can impose on you a little more. Two questions;

1, The Colour number you use isn't familiar to me. I recognise RGB and Hex, but not the number in your code. How can I work them out?
2, How could I change the font colour using your code? The whole dataset uses a lot of colours and some work better with the font as white.

 

[Micron]

If the number you need is a system number, one way:
use the immediate window in the vb editor with the correct sheet name and cell address. You do need the leading question mark.

?Sheets("Sheet 3").Range("A3").Interior.Color <<press enter.

If they are not system numbers:

Retrieve Excel Cell's Font & Fill RGB Color Code

Appearance can play a huge factor in the professionalism of your spreadsheet's presentation. Often there are times when I want to match a specific color that falls outside the current spreadsheet's Theme Color palette. There are also times when I want to match an Excel color so I can use it...

www.thespreadsheetguru.com

 

[QU4487]

If the number you need is a system number, one way:
use the immediate window in the vb editor with the correct sheet name and cell address. You do need the leading question mark.

?Sheets("Sheet 3").Range("A3").Interior.Color <<press enter.

If they are not system numbers:

Retrieve Excel Cell's Font & Fill RGB Color Code

Appearance can play a huge factor in the professionalism of your spreadsheet's presentation. Often there are times when I want to match a specific color that falls outside the current spreadsheet's Theme Color palette. There are also times when I want to match an Excel color so I can use it...

www.thespreadsheetguru.com

Very handy. So what is this colour code called? Obviously it isn't RGB or Hex

 

[saboh12617]

Very handy. So what is this colour code called? Obviously it isn't RGB or Hex

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