VBA alternative to conditional formatting

[LORDMARKS]

Hi all. I am working on a large data entry and reporting system in excel. Currently the area of drop down boxes covers 1.3 million cells and will probably end up double that. I was using excels inbuilt conditional formatting to grey out box's that are contain an "X" to improve the user interface, however this seems to greatly slow down the autofilters.

I have tried using the find replace function within VBA, as I am using it anyway, to replace the formatting aswell, but it doesnt seem to do anything?

What I am after is the most efficient way to look at a large range and apply 'Grey Background' formatting if the cell contains "X"


Thanks as always

 

[tlowry]

What I am after is the most efficient way to look at a large range and apply 'Grey Background' formatting if the cell contains "X"

I'm not clear on exactly what you want, so I did the simplest approach:

Sub test()
    With Worksheets("Sheet1").UsedRange
        .Replace What:="X", Replacement:="#N/A", _
                SearchOrder:=xlByColumns, MatchCase:=True
        .Cells.SpecialCells(xlConstants, xlErrors).Interior.ColorIndex = 15
        .Replace What:="#N/A", Replacement:="X", _
                SearchOrder:=xlByColumns, MatchCase:=True
    End With
End Sub

The above code will "grey" all cells that have a single, capital "X" and will go as fast as I know how to make it.