Hi,
I was hoping someone could help me with the following issue:
I have an input file with conditional formatting helping me to verify input data. However, since people will be able to add lines in the input template, this conditional formatting will be split into many once people add new lines (common excel issue).
Therefore, I as planning to use a macro to help me delete and replace all current conditional formatting. However, the file is split into different sections, so I need to make it dynamic.
Snapshot from file below:
I've added the following formatting in Excel
Asset001Currency = AND($A$3<>"";$B3<>"";$D3="")
Asset002Currency = AND($A$7<>"";$B7<>"";$D7="")
Asset003Currency = AND($A$11<>"";$B11<>"";$D11="")
I've added the following named ranges:
Asset001Currency =$E$3:$E$5
Asset002Currency =$E$7:$E$9
Asset003Currency =$E$11:$E$13
However, adding this as a VBA will not help me when new lines are added, as the conditional formatting formulas are hardcoded and doesn't change when new row's are added, even though the named range changes.
Example:
A new WBS is added for Asset 002, changing the named range for Asset002Currency to Asset002Currency = $E$7:$E$10 and Asset003Currency = $E$12:$E$14. The conditional formatting for Asset002 will still work, but not conditional formatting for Asset 003.
Therefore, I created new named ranges to help:
Asset001Cell = $A$3
Asset002Cell = $A$7
Asset003Cell = $A$11
My plan was to then write a code that picks the row number of the AssetXXXCell and used it to make the conditional formatting dynamic
Sub ConditionalFormatting()
Row = Range(Asset002Cell).Row
Application.Goto Reference:="Asset002Currency"
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(Asset002Cell<>"""";$B&Row&<>"""";$D&Row&="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13408767
.TintAndShade = 0
End With
I would have to add this condition for all three assets.
However, my VBA code doesn't work. Can anyone help me?
I was hoping someone could help me with the following issue:
I have an input file with conditional formatting helping me to verify input data. However, since people will be able to add lines in the input template, this conditional formatting will be split into many once people add new lines (common excel issue).
Therefore, I as planning to use a macro to help me delete and replace all current conditional formatting. However, the file is split into different sections, so I need to make it dynamic.
Snapshot from file below:
I've added the following formatting in Excel
Asset001Currency = AND($A$3<>"";$B3<>"";$D3="")
Asset002Currency = AND($A$7<>"";$B7<>"";$D7="")
Asset003Currency = AND($A$11<>"";$B11<>"";$D11="")
I've added the following named ranges:
Asset001Currency =$E$3:$E$5
Asset002Currency =$E$7:$E$9
Asset003Currency =$E$11:$E$13
However, adding this as a VBA will not help me when new lines are added, as the conditional formatting formulas are hardcoded and doesn't change when new row's are added, even though the named range changes.
Example:
A new WBS is added for Asset 002, changing the named range for Asset002Currency to Asset002Currency = $E$7:$E$10 and Asset003Currency = $E$12:$E$14. The conditional formatting for Asset002 will still work, but not conditional formatting for Asset 003.
Therefore, I created new named ranges to help:
Asset001Cell = $A$3
Asset002Cell = $A$7
Asset003Cell = $A$11
My plan was to then write a code that picks the row number of the AssetXXXCell and used it to make the conditional formatting dynamic
Sub ConditionalFormatting()
Row = Range(Asset002Cell).Row
Application.Goto Reference:="Asset002Currency"
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(Asset002Cell<>"""";$B&Row&<>"""";$D&Row&="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13408767
.TintAndShade = 0
End With
I would have to add this condition for all three assets.
However, my VBA code doesn't work. Can anyone help me?