Reputation: 11
I am trying to get some conditional formatting working in VBA for Excel 2013. The execution of the code should have the cell (interior color) in Col N turn Green if the list validation is "Complete" and white any other time. The cell (interior color) in Col O should turn Red if the list validation in Col N is "Held" and white any other time.
Currently, the results that appear are:
1. Col N and Col O are white if nothing is selected from the list validations.
2. Col N turns green when anything is selected from the list validations.
3. Col O turns red when "Held" is selected in Col N and turns white again if anything else in Col N is selected.
4. If something is selected in Col O, then the cell turns red.
My current code is (along with sections that I've commented out):
'Add conditional format for column N. If Status is "Complete", color Status cell (col N) green (43).
With Worksheets(SheetNum & " - Work").Range("N2:N2000").Select
'Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=OR(($N2=""Not Started""),($N2=""In Queue""), ($N2=""In Work""), ($N2=""Held""), ($N2="" "")"
'Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="=($N2=""Complete"")"
'With Selection.FormatConditions(1)
'.Interior.ColorIndex = 2
'.StopIfTrue = True
'End With
'End With
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="=($N2=""Complete"")"
With Selection.FormatConditions(1)
.Interior.ColorIndex = 43
'.StopIfTrue = True
End With
End With
'Add conditional format for column O. If Status is "Held", color Held For cell (col O)
'red (3).
'With Worksheets(SheetNum & " - Work").Range("O2:O2000").Select
'Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="=OR(($N2=""Not Started""),($N2=""In Queue""), ($N2=""In Work""), ($N2=""Complete""))"
'Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=($N2=""Held"")"
'With Selection.FormatConditions(1)
'.Interior.ColorIndex = 2
'.StopIfTrue = True
'End With
With Worksheets(SheetNum & " - Work").Range("O2:O2000").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="=($N2=""Held"")"
With Selection.FormatConditions(1)
.Interior.ColorIndex = 3
'.StopIfTrue = True
End With
End With
Also, can someone explain when to use Operator:=xlNotEqual vs Operator:=xlEqual? These seem to be working in reverse of what I would have expected.
Thanks for any help.
Upvotes: 0
Views: 3016
Reputation: 11
I have finally figured out the correct code to get what I want done.
'Add conditional format for column N. If Status is "Complete", color Status cell (col N) green (43).
With Worksheets(SheetNum & " - Work").Range("N2:N2000").Select
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=($N2=""Complete"")"
With Selection.FormatConditions(1)
.Interior.ColorIndex = 43
.StopIfTrue = True
End With
End With
Upvotes: 0
Reputation: 11
Unfortunately, these columns need to be colored on the fly which is why I'm trying to use conditional formatting. There are about 10 conditional formats that I'm trying to apply to these sheets which will be created throughout the life of the program.
On the plus side, after trying things all day, I noticed I was missing a ")" after (""Held""). It doesn't seem to have made the file work correctly, though.
Upvotes: 1
Reputation: 1198
Does the cell $N2 contain the actual words "Complete" and "Held" ?
If so maybe this will work
Sub tester()
For I = 1 To 100 ' or lastused row
If InStr(1, UCase(Sheet6.Range("N" & I).Value), "COMPLETE") > 0 Then
Sheet6.Range("N" & I).Interior.ColorIndex = 43
Else
If InStr(1, UCase(Sheet6.Range("N" & I).Value), "HELD") > 0 Then
Sheet6.Range("N" & I).Interior.ColorIndex = 3
Else
Sheet6.Range("N" & I).Interior.ColorIndex = 2
End If
End If
Next I
End Sub
Upvotes: 0