Solved Conditional Format VBA (1 Viewer)

[M3opa]

Hi all,

I am working on a project that creates a production plan in Excel based off data from an Access Database. This was working fine until I introduced conditional formatting into the mix. I found that the range for the conditional format kept changing when the data was updated within the spreadsheet. I tried to get around this by creating a macro that deletes and re-adds the conditional format upon refresh of the spreadsheet but this still did not work unfortunately.

After having a look online, I have decided to try and create VBA code that will update it for me but I am still very new to VBA and I am struggling to get it to work.

I have tried 2 variations so far.

Sub UpdateProdPlan()
'
' UpdateProdPlan Macro
'
' Keyboard Shortcut: Ctrl+m
'
    ActiveWorkbook.RefreshAll
    
    Dim rng As Range
    Dim condition1 As FormatCondition
    
    Set rng = Range("B7", "I56")
    
    rng.FormatConditions.Delete
    
    Set condition1 = rng.FormatConditions.Add(xlExpression, , "FinProdPlanExportQ!$K2=TRUE")
    
    With condition1
    .Font.Color = vbBlue
    End With
    
End Sub

Variation 2 looks something like this:

Sub UpdateProdPlan()
'
' UpdateProdPlan Macro
'
' Keyboard Shortcut: Ctrl+m
'
    ActiveWorkbook.RefreshAll
    
    Dim rng As Range
    
    Set rng = Range("B7", "I56")
    
    rng.FormatConditions.Delete
    
    .FormatConditions.Add Type:=xlExpression, Formula1:=""=FinProdPlanExportQ!$K2=TRUE""
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Interior.Color = vbBlue

End Sub

In order for this production plan to work, it must have conditional formatting so that production can see at a glance whether something is a hot fill etc.

Please bear with me VBA wise when answering, I really am keen to learn so any explanations as to where I am going wrong is greatly appreciated.

With thanks,

M3opa

 

[Gasman]

Here is something that someone helped me with in another forum.

In a sheet put your CF conditions.

Then code to run this is

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 Resume Next
    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 Resume Next
        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
            .FormatConditions.Add xlExpression, Formula1:=wsCond.Cells(lngCounter, 3).Value
            .FormatConditions(wsCond.Cells(lngCounter, 8).Value).Font.Color = 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

End Sub

 

[theDBguy]

Hi. Not sure if this will help you or not, but in one of my projects, I simply applied the conditional formatting to more rows than the users will ever need, so it doesn't matter when they have less or more data when they use the spreadsheet.

Just a thought...

 

[M3opa]

@Gasman, wow, that is a big wall of code! Starting to think I might be out of my depth with this. If I set my "CF" worksheet up exactly as above, is it a simple copy/paste job of the code itself?

@theDBguy, unfortunately not. There are already more rows than are needed. that data moves up/down the spreadsheet depending on if the date has moved or if something that has now been completed moves off etc.

 

[Gasman]

Yes.
That is the beauty of it. You just need to set the sheet exactly like shown, or view the thread commented in that code.
You will see from that thread that I got it wrong first time.
Also helps to understand the code, which I do, as I amended the delete CF portion to just affect the sheets I needed.

Edit: Thinking about it now, that code was probably in the code supplied.

However that is not a lot of code anyway.

 

[M3opa]

ok, thank you. I will give it a try and see how I get on.

 

[M3opa]

Whilst I struggle to comprehend the code you have given me, I look a little deeper into what my code was doing and noted the following:

Sub UpdateProdPlan()
'
' UpdateProdPlan Macro
'
' Keyboard Shortcut: Ctrl+m
'
    ActiveWorkbook.RefreshAll
    
    Dim rng As Range
    Dim condition1 As FormatCondition
    
    Set rng = Range("B7", "I56")
    
    rng.FormatConditions.Delete

    Set condition1 = rng.FormatConditions.Add(xlExpression, , "FinProdPlanExportQ!$K2=TRUE")
    
    With condition1
    .Font.Color = vbBlue
    End With
    
End Sub

The above does add a conditional format as I want it to except it shows up with quotation marks around the formula.

If I remove those quotation marks and apply, the formula works as needed. Unfortunately, removing the " in the code results in my getting told that $ is not a valid character. I tried to get around this with string concatenation

FinProdPlanExportQ! & "$" & K2 = TRUE but this did not work either.

It is annoying because I feel like my own code is so close to working and then it is something I understand and can troubleshoot if it ever goes wrong.

Any ideas how to remove quotation marks around the formula and still keep the $ symbol?

 

[Gasman]

I had the same problem if you read that thread.

 

[M3opa]

Yeah, I had a read through now, thank you.

colour wise, I keep getting the below issue:

This debugs down to here:

I get my colour numbers from custom section of the more colours option but these cannot be right. Am I missing something?

 

[M3opa]

Never mind, bit more investigating and I have it working now. I found some code to convert selected cell colour to its long number.

Now to test it on multiple formats!

 

[M3opa]

Sorry to add to my own thread again!

Would there be any way to update that so that it can remove borders from a range when another cell in the table is blank?

I used to have a CF rule set up where if the MO column is blank, the borders disappeared leaving a blank white space like you can see after the calendar starts.

I have looked through that code but only have the tiniest understanding of it and cannot work out how to turn borders off.

 

[CJ_London]

Record a macro that does what you want. Then inspect the code to see what it that turns borders off

 

[Isaac]

Hi all,

I am working on a project that creates a production plan in Excel based off data from an Access Database. This was working fine until I introduced conditional formatting into the mix. I found that the range for the conditional format kept changing when the data was updated within the spreadsheet. I tried to get around this by creating a macro that deletes and re-adds the conditional format upon refresh of the spreadsheet but this still did not work unfortunately.

After having a look online, I have decided to try and create VBA code that will update it for me but I am still very new to VBA and I am struggling to get it to work.

I have tried 2 variations so far.

Sub UpdateProdPlan()
'
' UpdateProdPlan Macro
'
' Keyboard Shortcut: Ctrl+m
'
    ActiveWorkbook.RefreshAll
   
    Dim rng As Range
    Dim condition1 As FormatCondition
   
    Set rng = Range("B7", "I56")
   
    rng.FormatConditions.Delete
   
    Set condition1 = rng.FormatConditions.Add(xlExpression, , "FinProdPlanExportQ!$K2=TRUE")
   
    With condition1
    .Font.Color = vbBlue
    End With
   
End Sub

Variation 2 looks something like this:

Sub UpdateProdPlan()
'
' UpdateProdPlan Macro
'
' Keyboard Shortcut: Ctrl+m
'
    ActiveWorkbook.RefreshAll
   
    Dim rng As Range
   
    Set rng = Range("B7", "I56")
   
    rng.FormatConditions.Delete
   
    .FormatConditions.Add Type:=xlExpression, Formula1:=""=FinProdPlanExportQ!$K2=TRUE""
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Interior.Color = vbBlue

End Sub

In order for this production plan to work, it must have conditional formatting so that production can see at a glance whether something is a hot fill etc.

Please bear with me VBA wise when answering, I really am keen to learn so any explanations as to where I am going wrong is greatly appreciated.

With thanks,

M3opa

Conditional formatting, even for more experienced VBA coders, is a GREAT example of when to use the Macro Recorder...to give you a start, of course, need to edit and correct all those Active, Activate, Select, Selection aspects.

 

[M3opa]

Yes, I used a Macro and managed to edit the code @Gasman provided to me to include the border options as well. All is working amazingly now and the thread is resolved.

A special thank you to @Gasman for his help in resolving this.