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]

I have just copied the sheet in question and the CF to a new workbook with the code and now it starts at row 7, not 4 as in the code

Even after re-running the "Set_CF" code?
I can do whatever I want to the "SF66OEK" sheet. Whenever I re-run that code, it clears out ALL the old ones and creates new ones, matching the conditions listed on the CF sheet.

 

[welshgasman]

Just run the CF a few times on that test sheet and now that does the stupidly high numbers?

I'll just go back to doing it manually, as I have no clue as to what is going on.

 

[Joe4]

If you want to upload a copy of your workbook, I would be willing to take a look at it a little bit later when I am off work and can download it.
Maybe I can see what is going on.

One other option may be to pre-format down to some row you will never hit, like 1000 or so. Then the Conditional Formatting should already contain those rows and you shouldn't need to rebuild the rules, as low as you inserts are within that range.

 

[welshgasman]

I *might* have found a solution from this link, Method 2
The first method is no good to me as I need it replicated down the rows.

First test gave me the correct addresses. Will run it several times later tomorrow.

Link is The conditional formatting is set incorrectly when using VBA in Excel - Office

So I added the Range(wsCond.Cells(lngCounter, 6).Value).Select which is the starting range.

        With rngToWork
            Range(wsCond.Cells(lngCounter, 6).Value).Select
            Debug.Print wsCond.Cells(lngCounter, 3).Value

 

[welshgasman]

If you want to upload a copy of your workbook, I would be willing to take a look at it a little bit later when I am off work and can download it.
Maybe I can see what is going on.

One other option may be to pre-format down to some row you will never hit, like 1000 or so. Then the Conditional Formatting should already contain those rows and you shouldn't need to rebuild the rules, as low as you inserts are within that range.

Hi Joe4,
That could be a good work around. However I do trim the sheet at the end of the month as well so as not to print off pages with blank rows, as I have totals at the bottom, so I just leave one blank row between the last row of data and the totals row. That is why I just do 5 at a time.

 

[welshgasman]

Joe,
How does one upload a file on this site?
I cannot see any mention of attach?

 

[Joe4]

That could be a good work around. However I do trim the sheet at the end of the month as well so as not to print off pages with blank rows, as I have totals at the bottom, so I just leave one blank row between the last row of data and the totals row. That is why I just do 5 at a time.

I would just print the range that is populate with data, and ignore all the blank lines at the bottom.
You can either do that manually, or set the print range via VBA.

How does one upload a file on this site?
I cannot see any mention of attach?

You cannot upload files to this site. You can upload them to a file sharing site, and then provide a link here for us to download.

 

[welshgasman]

I would just print the range that is populate with data, and ignore all the blank lines at the bottom.
You can either do that manually, or set the print range via VBA.


You cannot upload files to this site. You can upload them to a file sharing site, and then provide a link here for us to download.

I do set the print range via vba but as I mentioned there are totals at the bottom.
I just tested that workaround that I found again and I got stupid numbers again. Ran it again and got the correct ones again.
I will not upload the file at the moment, but thank you for the offer.

 

[welshgasman]

I have run the code several times again this morning, and so far it is producing the correct result. :Phew:

 

[welshgasman]

TBH, it seems a little hit and miss. I sometimes do not get the correct addresses, but only 2 or so rows out.
Just run it again and again until it gives the correct addresses. very, very strange.