Use CASE?

  • Thread starter Thread starter Risky Dave
  • Start date Start date
R

Risky Dave

Hi,

I have a piece of code that adds validation to specific cells in a row.

' Format effectiveness rating
Set rEffectiveness = Range("e" & lLineCount & ",H" & lLineCount & ",k" &
lLineneCount & ",n" & lLineCount & ",q" & lLineCount)
With rEffectiveness.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="Adequate, Improvement Required, Weak"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

For each valid value entered by the user, I want to conditionally format
the cell so that "Adequate" is Green, "Improvement Required" is Yellow and
"Weak" is Red (all font colours to be black).

This feels like a place to use a CASE statement, but I am not sure of the
syntax within VB, so can anyone suggest an approach?

TIA

Dave
 
Type in SELECT CASE in the macro, highlight the words, and click F1 that
will open up the help.

AGP
 
hi
i don't think select case would work here. select case choose one options
from a list. with conditional formating, you want all three. add this to the
end or your code.
Dim r As Range
Set r = Range("I3") 'change to suit
r.FormatConditions.Delete
r.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Adequate"""
r.FormatConditions(1).Interior.ColorIndex = 4
r.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""needs improvement"""
r.FormatConditions(2).Interior.ColorIndex = 6
r.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""weak"""
r.FormatConditions(3).Interior.ColorIndex = 3

regards
FSt1
 
Back
Top