Searching for a range of values in a column and selecting them

**AggyRJ** · 04-01-2013, 01:32 PM

I am at the end of a long macro I have been working on (thank you for all of those who have helped) and on the final page which sumarizes all of the information I would like to apply some conditional formatting to help the audience see what is going on. I have a column of percentages that start at J41 and run down several thousand rows (total amount will vary each time this report is run). I would like to apply color scales using conditional formatting but the exact type that I need does not seem to be available so I need to work around it by searching the column for a specific range of values and applying the formatting to that range. (Just in case someone knows how to do this in shorter steps, what I need to do is apply the "Green, Yellow, Red Color Scale" to all values under 0, , fill solid green to all values equal to 0, then the "Red, Yellow, Green Color Scale" to all values above 0).

What I think would work best is a command which searched the column starting at J41 and selected all values >0, then applied the formatting, then searched for all values equal to 0 and applied formatting, then searched for all values above 0 and applied formatting. I can do a Record Macro to get the steps for the formatting, but I do not know how to search a column for a range of values and select them to apply the formatting to. Any help would be appreciated.

**Mumps1** · 04-01-2013, 01:43 PM

Try:

Please Login or Register  to view this content.

**AggyRJ** · 04-01-2013, 02:25 PM

I appreciate the quick response, but I don't think that is going to get me where I need to be. It did select the range, but it did not give me the sliding scale effect that I was looking for - instead it just used three colors, one for negative values, one for zero, and one for positive values. To give you a little better idea what I am trying to do, I am going to paste a recorded script below which has the correct color scale effect applied. What I need is something that is going to define the proper range for me so that is is applied correctly. (In my example, the first line "Range("K6:K9").Select" instead needs to point to cell J41 and somehow search for all items less than zero and select those cells so that the color scale can be applied.

Please Login or Register  to view this content.

Again, thank you for the help.

**Mumps1** · 04-01-2013, 02:45 PM

I'm afraid that I don't don't have any experience in working with color scales. I did find the link below that may be of help to you.
http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

**AggyRJ** · 04-04-2013, 06:31 PM

I was able to solve the issue, thought I would post the code here in case anyone wanted to use it in the future.

Sub Conditional_Format ()

ActiveSheet.Range("J41", ActiveSheet.Range("J41").End(xlDown)).Select
ActiveSheet.Range("$J$41:$J$10397").AutoFilter Field:=10, Criteria1:="<0", _
Operator:=xlAnd
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueLowestValue
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 7039480
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValuePercentile
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 8711167
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueHighestValue
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 8109667
.TintAndShade = 0
End With
ActiveSheet.Range("$A$40:$J$10397").AutoFilter Field:=10
ActiveSheet.Range("J41", ActiveSheet.Range("J41").End(xlDown)).Select
ActiveSheet.Range("$J$41:$J$10397").AutoFilter Field:=10, Criteria1:="0%"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveSheet.Range("$J$41:$J$10397").AutoFilter Field:=10
ActiveSheet.Range("J41", ActiveSheet.Range("J41").End(xlDown)).Select
ActiveSheet.Range("$J$41:$J$10397").AutoFilter Field:=10, Criteria1:=">0", _
Operator:=xlAnd
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueLowestValue
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 8109667
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValuePercentile
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 8711167
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueHighestValue
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 7039480
.TintAndShade = 0
End With
ActiveSheet.Range("$A$40:$J$10397").AutoFilter Field:=10

End Sub