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.
Variation 2 looks something like this:
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
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.
Code:
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:
Code:
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