Specific range formating
 

I have a sheet where I only want the code to effect certain parts of rows
(ie. D6:G6, D10:G10, D14:G14, etc). I want to apply conditional formatting.
The CF function only allows 3 conditions, and I need 4 so I thought I would
try it with VBA.
I want to look at each of the cells in the specified ranges and if it's
greater than 95, a certain color, between 90 and 95 another color, less than
90 a different color and if the cell has "N/A", to be black.

Thanks!

Specific range formating
 

Try using this Worksheet Change event code in your sheet's code window....

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count = 1 Then
If Application.WorksheetFunction.IsNA(.Value) Then
.Interior.Color = vbBlack
ElseIf Application.WorksheetFunction.IsErr(.Value) Then
.Interior.Color = RGB(127, 127, 127)
ElseIf .Value Like "*[!0-9]*" Then
.Interior.ColorIndex = xlNone
ElseIf .Value 95 Then
.Interior.Color = vbGreen
ElseIf .Value 89 Then
.Interior.Color = vbYellow
Else
.Interior.Color = vbRed
End If
End If
End With
End Sub

You didn't say what to do if an error other than #N/A were to occur, so I
made those cells a dark gray, were such a condition happen.

Rick


"aimee209" wrote in message
...
I have a sheet where I only want the code to effect certain parts of rows
(ie. D6:G6, D10:G10, D14:G14, etc). I want to apply conditional
formatting.
The CF function only allows 3 conditions, and I need 4 so I thought I
would
try it with VBA.
I want to look at each of the cells in the specified ranges and if it's
greater than 95, a certain color, between 90 and 95 another color, less
than
90 a different color and if the cell has "N/A", to be black.

Thanks!

Specific range formating
 

I think my test for numeric values is too strict (it requires numeric
entries to be integer values; that is, not floating point). Here is a more
general test allowing all numeric values to be treated as numeric...

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count = 1 Then
If Application.WorksheetFunction.IsNA(.Value) Then
.Interior.Color = vbBlack
ElseIf Application.WorksheetFunction.IsErr(.Value) Then
.Interior.Color = RGB(127, 127, 127)
ElseIf Not IsNumeric(.Value) Then
.Interior.ColorIndex = xlNone
ElseIf .Value 95 Then
.Interior.Color = vbGreen
ElseIf .Value 89 Then
.Interior.Color = vbYellow
Else
.Interior.Color = vbRed
End If
End If
End With
End Sub

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Try using this Worksheet Change event code in your sheet's code window....

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count = 1 Then
If Application.WorksheetFunction.IsNA(.Value) Then
.Interior.Color = vbBlack
ElseIf Application.WorksheetFunction.IsErr(.Value) Then
.Interior.Color = RGB(127, 127, 127)
ElseIf .Value Like "*[!0-9]*" Then
.Interior.ColorIndex = xlNone
ElseIf .Value 95 Then
.Interior.Color = vbGreen
ElseIf .Value 89 Then
.Interior.Color = vbYellow
Else
.Interior.Color = vbRed
End If
End If
End With
End Sub

You didn't say what to do if an error other than #N/A were to occur, so I
made those cells a dark gray, were such a condition happen.

Rick


"aimee209" wrote in message
...
I have a sheet where I only want the code to effect certain parts of rows
(ie. D6:G6, D10:G10, D14:G14, etc). I want to apply conditional
formatting.
The CF function only allows 3 conditions, and I need 4 so I thought I
would
try it with VBA.
I want to look at each of the cells in the specified ranges and if it's
greater than 95, a certain color, between 90 and 95 another color, less
than
90 a different color and if the cell has "N/A", to be black.

Thanks!