Use CASE?

[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

 

[AGP]

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

AGP

 

[FSt1]

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

 

[Risky Dave]

AGP, FSt1

My thanks. Excatly what I needed

Dave