Conditional Formatting with VBA getting incorrect row values in final CF

[welshgasman]

Hi all,
I had some help using Conditional Formatting in VBA in this thread. VBA apply conditional formatting

I have still not been able to work out what is going on.
Does anyone have any clue please.

Basically Excel is replacing my target range with a very high number, near the limit of Excel rows.
I have walked through the code and checked that the values from the worksheet cells are passed in correctly, but those high numbers still get in there.

If I cannot get to the bottom of it, would anyone know a workaround?

Sub Set_CF()
' https://www.mrexcel.com/board/threads/vba-apply-conditional-formatting.1220882/
' Created: 20221101
' By:      HaHoBe
' Version: 1
' Updated: 20221117
' Reason:  allowing more than one condition for CF
    Dim lngCounter As Long
    Dim rngToWork As Range
    Dim ws As Worksheet
    Dim wsCond As Worksheet
    Dim wsTarg As Worksheet

    On Error GoTo Error_CF
    Set wsCond = Worksheets("CF")
    If wsCond Is Nothing Then GoTo end_here
    On Error GoTo 0

    'Remove old conditional formatting
    '  For Each Ws In ThisWorkbook.Worksheets
    '    If Ws.Name <> wsCond.Name Then
    '      Ws.Cells.FormatConditions.Delete
    '    End If
    '  Next Ws
    ' Clear formatting
    '/// I would recommend to use the tab name here instead of any position
    Worksheets("SF66OEK").Cells.FormatConditions.Delete
    Worksheets("Passengers").Cells.FormatConditions.Delete
   
    'Set new formatting
    For lngCounter = 2 To wsCond.Range("A" & Rows.Count).End(3).Row
        On Error GoTo Error_CF
        Set wsTarg = Worksheets(wsCond.Cells(lngCounter, 1).Value)
        If wsTarg Is Nothing Then GoTo end_here
        Set rngToWork = wsTarg.Range(wsCond.Cells(lngCounter, 6).Value, wsCond.Cells(lngCounter, 7).Value)
        If rngToWork Is Nothing Then GoTo end_here
        On Error GoTo 0
        With rngToWork
            Debug.Print wsCond.Cells(lngCounter, 3).Value
            .FormatConditions.Add xlExpression, Formula1:=wsCond.Cells(lngCounter, 3).Value
            .FormatConditions(wsCond.Cells(lngCounter, 8).Value).Font.ColorIndex = wsCond.Cells(lngCounter, 5).Value
            .FormatConditions(wsCond.Cells(lngCounter, 8).Value).Interior.Color = wsCond.Cells(lngCounter, 4).Value
            .FormatConditions(wsCond.Cells(lngCounter, 8)).StopIfTrue = False

        End With
    Next lngCounter

end_here:
    If wsCond Is Nothing Then
        MsgBox "Check the name of the sheet with the parameters.", vbExclamation, "Name of sheet with parameters"
    ElseIf wsTarg Is Nothing Then
        MsgBox "Check the name of the target sheet", vbExclamation, "Could not find sheet"
    ElseIf rngToWork Is Nothing Then
        MsgBox "Could not build a range to work on, please check addresses.", vbExclamation, "Problems building range"
    End If

    Set rngToWork = Nothing
    Set wsTarg = Nothing
    Set wsCond = Nothing
    MsgBox "CF now reset on " & ActiveWorkbook.Name
    Exit Sub
Error_CF:
    MsgBox "Error in CF module " & Err.Description & " - " & Err.Number
    Resume end_here
End Sub

 

[Joe4]

Seems only one rule can be applied.
Yes, even if the passenger has a Blue Badge, I still want some indication that the NOF applies as well.
If you change K36 to Yes, we lose the NOF CF.

I will have to add multiple conditions to each rule, if that is the case.

I believe I could use Bold/Italic instead as an indicator. If I make the NOF rule Bold before I change K36, I get Green, but the Bold remains.

I will give that a play.

Thanks for looking Joe.

Yeah, I just did some basic testing, setting 5 different rules on a single cell, and was able to get all 5 to work. So not sure why your current set-up doesn't work that way too (I wonder if it is an Excel version thing).

However, the workaround I gave you should work, as long as those are the only ones giving you issues. If you need other combinations to work (and they are not), it may be back to the drawing board!

 

[welshgasman]

Nope, does not work with Bold/Italic etc.

Seems as soon as one rule is applied, no others can take effect.
Makes me wonder what the Stop is True is for.
I will look at multiple conditions each rule, but if I had another one to apply it is going to get very messy.

 

[welshgasman]

Yeah, I just did some basic testing, setting 5 different rules on a single cell, and was able to get all 5 to work. So not sure why your current set-up doesn't work that way too (I wonder if it is an Excel version thing).

However, the workaround I gave you should work, as long as those are the only ones giving you issues. If you need other combinations to work (and they are not), it may be back to the drawing board!

Could be because my version is so old. I can add rules/conditions manually to same cell
I will Google some more.

Thanks for testing.

I think I will just check my notes each time. Seems a lot of work for the odd trip.

 

[Joe4]

Seems as soon as one rule is applied, no others can take effect.

That is the odd thing. That is how your workbook seems to be behaving, but if I open up a new workbook on my computer, and enter a value like 10 in cell A1, and then write 5 different rules on cell A1 that all evaluate to TRUE (i.e. =A1>1, =A1>2, etc), all 5 formats are applied!

So I might think it might be a version issue. I found an old post that said you can only apply 3 different Conditional Formatting Rules to the same range of cells, and you have 5, but I am not sure what Excel versions that applies to, as I don't see that happening in 365.

Make sme wonder what the Stop is True is for.

A few reasons:
- To control the order/predence of your rules (put your highest priority ones at the top)
- To save from needless checks (once it finds a TRUE one, stop there - there is no reason to check the others)
- When you have multiple rules, it makes it easier to organize and read them (and possibly debug them) and understand what the intention is

 

[RoryA]

You can apply multiple conditional formats as long as they don't contradict each other. If you have two rules that both apply a fill colour for example, obviously they can't both work. But if one uses a fill colour and another a font colour, then they can both apply at once (unless you used Stop if true).

 

[Joe4]

You can apply multiple conditional formats as long as they don't contradict each other. If you have two rules that both apply a fill colour for example, obviously they can't both work. But if one uses a fill colour and another a font colour, then they can both apply at once (unless you used Stop if true).

Yeah, that is the weird thing Rory - the two rules he is having issues with don't appear to be contradicting each other.
They are checking values in different cells, and one is changing font color, and the other background color.
Really bizarre!

 

[welshgasman]

Well I need the Disabled Badge notification more than the NOF. TBH that happens very infrequently, but with my memory, I need some indicator.
I will just check the notes column.
Yes, could well be the version. I have similar in Access.

MS have probably updated several features over the years.
For the most part 2007 suits my needs.
Hell I am still using the last version of Quicken before they dumped us in the UK. That is XG 2004.

 

[Joe4]

I created a new worksheet and set-up the two Conditional Formatting Rules in question, and they both seemed to work in conjunction with each other.
I wonder if setting up the rules via VBA has something to do with it.
Maybe the rule that set-up font color with no background color is explicitly telling to remove all background color (thereby creating that conflict that Rory mentioned).

 

[RoryA]

the two rules he is having issues with don't appear to be contradicting each other

They are in the code posted here - all the rules set both a font.colorindex and an interior.color

 

[Joe4]

They are in the code posted here - all the rules set both a font.colorindex and an interior.color

Well, there it is! Nice detective work, Rory.

So he would need to write the rules to only change the things he wants changed (either Font.ColorIndex or Interior.Color), but not both for each and every rule or else that last one is going to win and overwrite everything prior to it.

welshgasman,
Maybe add two new columns, True/False fields for Update Font and Update Background, then code it into your script to only apply that part for each formula if those fields are True.