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:

Sub Test()
    Application.ScreenUpdating = False
    Dim bottomJ As Integer
    bottomJ = Range("J" & Rows.Count).End(xlUp).Row
    Dim c As Range
    For Each c In Range("J41:J" & bottomJ)
        If c <> "" Then
            If c < 0 Then
                c.Interior.ColorIndex = 3
            ElseIf c = 0 Then
                c.Interior.ColorIndex = 6
            ElseIf c > 0 Then
                c.Interior.ColorIndex = 4
            End If
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

**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.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("K6:K9").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
    Range("K10").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("K11:K14").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
End Sub

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