Hi all
On the attached spreadsheet I have a macro setup that, depending on which colour cell is selected in row 2, should then use conditional formatting to fill all cells that are not of the selected colour in white and make the font white (i.e. to blank out all cells that are not that colour). All works fine in that when you select the relevant colour cell in row 2, the conditional formatting is added when you click the macro button (in the top left corner), but no formatting is applied (i.e. white cell fill and white font) - if I go into manage rules and add the white font and white fill it works fine, so not sure why it is not adding it via the macro. Can anyone help me with how to correct this please as I am stumped?
Thanks
Dave
Try this:
Sub Macro1()
'
' Macro1 Macro
'
'
Dim Colour
Colour = ActiveCell.Interior.Color
Application.ScreenUpdating = False
With Range("B5:AP75")
.Worksheet.EnableFormatConditionsCalculation = False
.Select
With .FormatConditions
.Delete
On Error Resume Next
ThisWorkbook.Names("GetColor").Delete
On Error GoTo 0
ThisWorkbook.Names.Add Name:="GetColor", RefersToR1C1:="=IdentifyColor(RC)"
Dim fc As Object
Set fc = .Add(Type:=xlExpression, Formula1:="=AND(GetColor<>0,GetColor<>14277081,GetColor<>10921638,GetColor<>255,GetColor<>" & Colour & ")")
End With
With fc
.SetFirstPriority
.Font.Color = RGB(255, 255, 255)
.Interior.Color = RGB(255, 255, 255)
.StopIfTrue = False
End With
.Worksheet.EnableFormatConditionsCalculation = True
End With
Range("B4").Select
End Sub
Function IdentifyColor(CellToTest As Range)
On Error Resume Next
IdentifyColor = CellToTest.Interior.Color
End Function
Note: it's probably going to perform badly since you have multiple udf calls per cell and conditional formatting is volatile. I suspect you'd be better off using VBA to apply direct formatting to the cells.
That's perfect for what I need - thanks so much 🙂 It's only to be used to view certain coloured info and then switched off again, so it doesn't matter that it is slightly clunky as will only be used every now and then.
