I'm trying to use a VBA macro to assign conditional formatting as it needs to be done over several different excel documents.
I have three columns, for the project name, if it is started and if it is completed.
What I want if for the project name and started column to change to orange is started is TRUE and all three cells to change to green is completed if TRUE.
I am using a separate macro to auto generate checkboxes which are linked to the started and completed cells but in effect they act as if the cell is either TRUE/FALSE.
This is the code I have so far but currently both the Range and Started Cells change colour independently if set to true.
What would I need to change so that the name is changed based on the true/false in the cell next to it? While also changing the cell next to it.
I have three columns, for the project name, if it is started and if it is completed.
What I want if for the project name and started column to change to orange is started is TRUE and all three cells to change to green is completed if TRUE.
I am using a separate macro to auto generate checkboxes which are linked to the started and completed cells but in effect they act as if the cell is either TRUE/FALSE.
This is the code I have so far but currently both the Range and Started Cells change colour independently if set to true.
What would I need to change so that the name is changed based on the true/false in the cell next to it? While also changing the cell next to it.
VBA Code:
[
Sub Format()
Dim MyRange As Range
Set MyRange = Range("E1:E10") 'Change to required Range
Dim Started As Range
Set Started = MyRange.Offset(0, 1)
'Dim Completed As Range
'Set Started = MyRange.Offset(0, 2)
'Apply Conditional Formatting If Started - Should colour MyRange and Started
With Started
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:=True
.FormatConditions(1).Interior.Color = RGB(255, 192, 0)
End With
With MyRange
.FormatConditions.Add Type:=xlExpression, Formula1:="=" & MyRange(1).Address(0, 1) & "= " & True
.FormatConditions(1).Interior.Color = RGB(255, 192, 0)
End With
'Apply Conditional Formatting If Completed - Should colour MyRange and Started and Completed In different colour
End Sub
]