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

"Risky Dave" wrote in message
...
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

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" wrote:

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, FSt1

My thanks. Excatly what I needed :-)

Dave

"FSt1" wrote:

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" wrote:

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