Reference to a Range not working for conditional formatting

[Malick]

Hi,

I am trying to reference a range in a table, to apply conditional formatting through VBA, but unfortunately it is not working. I am getting my column names of the table by running the FOR EACH loop, and saving the results in 'FormulaNum' string variable. I'm trying to use this variable as a RANGE , when i set my range (rRangeToFormat), but it doesn't work. If i hard code the column names of my table, while setting the range, it does work. Below is my code. Thank you for providing help on this.

Dim RngFormula As Range    
Dim wsFormula As Worksheet
    Dim FormulaNum As String

    Set wsFormula = Worksheets("VB")
    For Each RngFormula In wsFormula.Range("TblSec")
        If Not IsEmpty(RngFormula) Then
        FormulaNum = FormulaNum & "tbl_1[" & RngFormula & "],"
        End If
    Next RngFormula
    FormulaNum = Left(FormulaNum, Len(FormulaNum) - 1)
    'MsgBox FormulaNum
    Set rRangeToFormat = Worksheets("Vocab").Range(FormulaNum)

 With rRangeToFormat
        .FormatConditions.Delete
        .FormatConditions.AddColorScale ColorScaleType:=3

 

[TMShucks]

 With rRangeToFormat
        .Select
        .FormatConditions.Delete
        .FormatConditions.AddColorScale ColorScaleType:=3

 

[Malick]

Hi TMS,
Thank you for your reply, but
.Select doesn't work either.

 

[Malick]

I have provided the complete code below, including the one for conditional formatting. Although I guess the line below is producing 'Nothing' as far as the RANGE is cocerned.

Set rRangeToFormat = Worksheets("Vocab").Range(FormulaNum)

Thanks for providing any help as I really struggle to sort this out.

Dim RngFormula As Range    
Dim wsFormula As Worksheet
    Dim FormulaNum As String


    Set wsFormula = Worksheets("VB")
    For Each RngFormula In wsFormula.Range("TblSec")
        If Not IsEmpty(RngFormula) Then
        FormulaNum = FormulaNum & "tbl_1[" & RngFormula & "],"
        End If
    Next RngFormula
    FormulaNum = Left(FormulaNum, Len(FormulaNum) - 1)
    'MsgBox FormulaNum
    Set rRangeToFormat = Worksheets("Vocab").Range(FormulaNum)


 With rRangeToFormat
.Select
        .FormatConditions.Delete
        .FormatConditions.AddColorScale ColorScaleType:=3
    
        .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValuePercent
        .FormatConditions(1).ColorScaleCriteria(1).FormatColor.Color = RGB(99, 190, 123)
        .FormatConditions(1).ColorScaleCriteria(1).FormatColor.TintAndShade = 0.25


        .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercent
        .FormatConditions(1).ColorScaleCriteria(2).value = 50
        .FormatConditions(1).ColorScaleCriteria(2).FormatColor.Color = RGB(255, 235, 132)
        .FormatConditions(1).ColorScaleCriteria(2).FormatColor.TintAndShade = 0


        .FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValuePercent
        .FormatConditions(1).ColorScaleCriteria(3).FormatColor.Color = RGB(248, 105, 107)
        .FormatConditions(1).ColorScaleCriteria(3).FormatColor.TintAndShade = -0.25
    End With

 

[TMShucks]

To be honest, it's still less than half a story. What does the named range VB refer to and what's in it? What does the table reference refer to and what's in it? What does the code build? And how is that different to the hard coded column references? What does the working hard coded code look like?

What does "it doesn't work" mean? Does it do nothing? Does it apply the CF but to the wrong cells? Does it crash? Lock up?