Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating Specific Text | Excel Discussion (Misc queries) | |||
Count If Specific word in specific range | Excel Discussion (Misc queries) | |||
CONDITIONAL FORMATING FINDING TEXT CONTAINING SPECIFIC NUMBERS | Excel Worksheet Functions | |||
conditional formating a specific amount of cells in a row. | New Users to Excel | |||
search a series of rows for a specific formating | Excel Worksheet Functions |