Conditional formatting limit

This code is triggered when somebody changes something on a page and only tests the cells which have been changed.

How about we make a button on page 2 to refresh all formats?

Sounds good Cheeky but how do you refresh all formats?

Just a thought . . . not tested

Why not place a volatile function like NOW() in an out of the way cell on sheet2, then in the worksheet_activate module force it to recalculate which should trigger the code.

Private Sub Worksheet_Activate()
    Application.Calculate
End Sub

You make a macro which searches for all instances of C, Z, V and Maternity and applies the appropriate forrmat to every found instance...

This will work best if you define a range to search, so that we don't hose the format for the whole sheet (select all the cells in the table which contains C, Z, etc. then, in the top left, just above "A" column heading type a name: "FormatRange" (without quotes).

Now put this code into the worksheet:

Sub AutoFormatRange()

Dim vStrings(0 To 3, 0 To 1) As Variant
Dim i As Integer
Dim rString As Range
Dim sFirstAddress As String

vStrings(0, 0) = "C"
vStrings(0, 1) = 3
vStrings(1, 0) = "Z"
vStrings(1, 1) = 4
vStrings(2, 0) = "V"
vStrings(2, 1) = 5
vStrings(3, 0) = "Maternity"
vStrings(3, 1) = 7


With Me
    .Range("FormatRange").Interior.ColorIndex = xlNone
    For i = LBound(vStrings) To UBound(vStrings)
        Set rString = .Range("FormatRange").Find(vStrings(i, 0))
        If rString Is Nothing Then GoTo lNoString
            sFirstAddress = rString.Address
            Do
                rString.Interior.ColorIndex = vStrings(i, 1)
            Set rString = .Range("FormatRange").FindNext(rString)
            Loop Until rString.Address = sFirstAddress
lNoString:
    Next i
End With
    
End Sub

CC

Thanks for the replies you all.
I tried your code Cheeky but get an compile error "Invalid use of Me keyword"

I did what you said. First defined the range and called it "FormatRange" but nothing...

I did. [forgot to mention that]

As written, it needs to go in the Sheet module.

I did. [forgot to mention that]

I don't want to patronise you, of course, but are you sure?
Right-click sheet tab and select View code - paste in there.

Tested befor posting and worked beautifully IMHO

Sorry for my ignorance guys, You were right I placed it in a standard module.
Now that I placed it in the right place it still doesn't work properly.

Like I mentioned in my first post sheet2 contains formulas which retrieve information from sheet1. it is in sheet1 where I update the cells. But the color of the cells on sheet2 should change.

I placed both the code I mentioned in my first post and your code separately in the sheet-code and made a button which triggers your code.

When I enter a "V" on sheet2 and press the button the color of the cell is changed indeed. But as I mentioned already sheet2 only contains formulas

oh no!

how stupid of me

how big is the range? We can test every cell individually but it will take time if the range is too big

CC

The range is quite big E27:BA200.

Oh wait, it's not that bad...

XL help:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

Revised code, includes setting to look in cell values, not formulae:

Sub AutoFormatRange()

Dim vStrings(0 To 3, 0 To 1) As Variant
Dim i As Integer
Dim rString As Range
Dim sFirstAddress As String

vStrings(0, 0) = "C"
vStrings(0, 1) = 3
vStrings(1, 0) = "Z"
vStrings(1, 1) = 4
vStrings(2, 0) = "V"
vStrings(2, 1) = 5
vStrings(3, 0) = "Maternity"
vStrings(3, 1) = 7


With Me
    .Range("FormatRange").Interior.ColorIndex = xlNone
    For i = LBound(vStrings) To UBound(vStrings)
        Set rString = .Range("FormatRange").Find(vStrings(i, 0), LookIn:=xlValues, lookat:=xlWhole)
        If Not rString Is Nothing Then
            sFirstAddress = rString.Address
            Do
                rString.Interior.ColorIndex = vStrings(i, 1)
            Set rString = .Range("FormatRange").FindNext(rString)
            Loop Until rString.Address = sFirstAddress
        End If
    Next i
End With
    
End Sub

Also includes revision to avoid unnecessary label, an improvement I achieved with no help from anyoen else.

Please try this.

CC

CC this is great, You are brilliant. It works like a charm.
Thank you very much. You really helped me with this code.

CC, I tried to change one of the cases into something like "1 To 10" but it didn't work.

Is it possible to amend one of the rules, so it will change the color of cells with a number between 1 and 10?

BTW is there a way to let this code overrule standard Conditional Formatting rules?

You could use it to remove conditional formatting - but not temporarily override it, which I think is the point. If you were feeling particularly obtuse you could use a spare hidden sheet to hold conditional format info - but this would be a lot of work for very little reward...

re: ranges of numbers, not with this code, but you could use conditional formatting for that, which, as we've discussed, overrides normal formatting

CC

I see, Thanks again for your help CC