Specific range formating

  • Thread starter Thread starter aimee209
  • Start date Start date
A

aimee209

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!
 
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
 
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) said:
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 said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top