VBA Excel, 3ColorScale, and 2 Variables

[JenniferNash]

Help! I have spent DAYS trying to figure this out. Disclaimer - I don't know VBA and have been trying to figure this out.
This part is working perfectly:

Sub ZScoreCF1()
Range("AD10:AD154").Select
Application.CutCopyMode = False
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(1).Value = -1.282
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 0
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(3).Value = 1.282
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.ThemeColor = xlThemeColorDark2
.TintAndShade = 0
End With
End Sub

I want to be able to do this same thing for Column AD with the addition of another variable in Column L.
Column L has categories (ex: Equity or Patient Centeredness). If Column L has Patient Centeredness, then and only then do I want the above to be applied.
Thank you so much for your expertise!
Jennifer

 

[OaklandJim]

I suspect that you got no responses because it is challenging to understand your need. Ideally you could provide a link to the file that you are trying to modify. You use the link icon above (shows two chain links) to do that. Don't post personal data but if practical make fake but realistic data. That gives list helpers more to work with.

Maybe try again to describe the need differently.

If you think that it would be helpful to show a portion of your worksheet (where the data is) you can do that using MrExcel's excellent addin called XL2BB. It is described here: XL2BB - Excel Range to BBCode

This may not be helpful but I tried to modify code so it still works but is a bit easier to read, stylewise. One key theme: no need to select a range to do stuff to it. Also adding comments is often helpful for people trying to assist.

BTW, I ran my version of the code and nothing in the range AD10:AD154 seemed to change. Maybe I broke it. Nonetheless, you might pick up some style ideas from the rewrite.

Sub ZScoreCF1()

'   Worksheet object for worksheet containing data.
    Dim wsDataSheet As Worksheet
    
'   Range object for cells containing data.
    Dim rDataRange As Range
    
'   Point the range object to the worksheet containing data.
    Set wsDataSheet = ThisWorkbook.Worksheets("Sheet1")  '<= change if the sheet has another name.
    
'   Point the worksheet object to the range where data is located.
    Set rDataRange = wsDataSheet.Range("AD10:AD154")

'   Process the data range.
    With rDataRange
    
        .FormatConditions.AddColorScale ColorScaleType:=3
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        
        .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueNumber
        .FormatConditions(1).ColorScaleCriteria(1).Value = -1.282
        
        With .FormatConditions(1).ColorScaleCriteria(1).FormatColor
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0
        End With
        
        .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValueNumber
        .FormatConditions(1).ColorScaleCriteria(2).Value = 0
        
        With .FormatConditions(1).ColorScaleCriteria(2).FormatColor
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        
        .FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueNumber
        .FormatConditions(1).ColorScaleCriteria(3).Value = 1.282
        
        With .FormatConditions(1).ColorScaleCriteria(3).FormatColor
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = 0
        End With
    
    End With 'rDataRange
    
    Application.CutCopyMode = False

Debug.Print "here"

End Sub

 

[JenniferNash]

Thank you!

Finally found a solution too:

Sub PCCF()
Dim cell As Range
For Each cell In Range("L10:L3081")
If cell.Value = "Patient Centeredness" Then
cell.Offset(0, 18).FormatConditions.AddColorScale ColorScaleType:=3
cell.Offset(0, 18).FormatConditions(cell.Offset(0, 18).FormatConditions.Count).SetFirstPriority
cell.Offset(0, 18).FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueNumber
cell.Offset(0, 18).FormatConditions(1).ColorScaleCriteria(1).Value = -1.282
With cell.Offset(0, 18).FormatConditions(1).ColorScaleCriteria(1).FormatColor
.ThemeColor = xlThemeColorDark2
.TintAndShade = 0
End With

cell.Offset(0, 18).FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValueNumber
cell.Offset(0, 18).FormatConditions(1).ColorScaleCriteria(2).Value = 0
With cell.Offset(0, 18).FormatConditions(1).ColorScaleCriteria(2).FormatColor
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With

cell.Offset(0, 18).FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueNumber
cell.Offset(0, 18).FormatConditions(1).ColorScaleCriteria(3).Value = 1.282
With cell.Offset(0, 18).FormatConditions(1).ColorScaleCriteria(3).FormatColor
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0
End With
End If

Next cell

End Sub