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]

Are you sure that you do not have anything running that automatically inserts or deletes rows?
Note that you can call the "Set_CF" procedure automatically at the end of your "Insert Rows" procedure, so you don't need to run the second manaually.

 

[welshgasman]

Yes,
I even copied the sheet to a new workbook with the CF code.
Good point. However I need a method to run it manually if it decides to put a incorrect row number in the formulae.

As it has happened to the Test sheet as well, I will upload that when I have a moment and find a file sharing site.

 

[welshgasman]

Here you go.

Test CF.xlsm

Shared with Dropbox

www.dropbox.com

 

[welshgasman]

Ok, now having to revisit this.
I would like to add another CF rule. This time it is to fill the cells if something exists in another cell.
However it seems to conflict with other rules, despite the Stop if True being false for all rules.

If a passenger has a Disabled Blue Badge a rule changes the font to Green, with White background. That works fine.
If a passenger has given me an exhorbitant tip, I wish to mark them as having the next trip free (as long as the suplus will cover the next trip of course, but the maths is not the problem)
If the passenger does not have a Blue Badge, then the rule works fine, however if they do, then the rule does not take effect and they just look like having a Blue Badge?

How can I combine the rules please? The rule in question is NOF (Next One Free )

This is the CF sheet now

 

[Joe4]

If a passenger has given me an exhorbitant tip, I wish to mark them as having the next trip free (as long as the suplus will cover the next trip of course, but the maths is not the problem)
If the passenger does not have a Blue Badge, then the rule works fine, however if they do, then the rule does not take effect and they just look like having a Blue Badge?

How can I combine the rules please? The rule in question is NOF (Next One Free )

Can you upload a file with an example of one that is not working properly?

 

[welshgasman]

Yes, I will use that last one I uploaded to Dropbox,
I will post back when it has been replaced.

I have just discovered that this applies to any rule.
If a row has a rule applied, subsequent rules do to work.

 

[welshgasman]

OK new file uploaded. Called the same name.
You will see that a row is filled yellow. That is the new rule for NOF.
It works only if no other rule has been applied to that row.
If you enter NOF in column N for any that re green font (Blue Badge) it does not work.

Thank you for looking.
Test workbook

Test CF.xlsm

 

[welshgasman]

Yes, I will use that last one I uploaded to Dropbox,
I will post back when it has been replaced.

I have just discovered that this applies to any rule.
If a row has a rule applied, subsequent rules do to work.

That should be 'do not work'

 

[Joe4]

In looking at your file, your rules seem to work perfectly fine.

On line 36, the "NOF" rule is the only one that evaluates to TRUE, so we would expect it to apply that yellow background formatting.
If we change the value in cell K36 to "Yes", then your Blue Badge condition evaluates to TRUE, and it formats the text to Green.

Is the issue that you want BOTH Conditional Formatting rules to be applied at the same time?
I believe only the last one that evaluates to TRUE is applied.
I think you may need to add another Rule like:

=AND($K4="Yes",LEFT($N4,3)="NOF")

and have that apply BOTH the green font format and the yellow background format.
Place that one at the very top, and check the "Stop if True" box.

 

[welshgasman]

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.