Select a cell within a formula with its position related to its column name in a data table

Question

I have a very particular issue within my VBA code. And I didn't find any helpful topics on any forum.

Anyway here is the issue. I have a macro that is supposed to generate conditional formatting for my spreadsheet.

'Set segment_heading = Sheets("TRT RTI Challenges").Cells.Find("SEGMENT PCC")
'Range(Cells(2, r.Column), Cells(500, r.Column)).Select
Range("A2:AG3000").Select

'Mise en forme conditionnelle 2
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=ET($M2=""Bleu"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 16636367
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

As you can see, my formatting depend on the formula "=ET($M2=""Bleu"")" The thing is that column M has a specific name, let's say "Colors". Sometimes, columns are moving

score 0 · Accepted Answer · answered Jun 06 '19 at 14:06

This should do it:

Option Explicit
Sub Test()

    With ThisWorkbook.Sheets("TRT RTI Challenges").Range("A2:AG3000")
    'Mise en forme conditionnelle 2
        .FormatConditions.Add Type:=xlExpression, Formula1:=MyFormula("Colors")
        '"=ET($M2=""Bleu"")"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 16636367
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With

End Sub
Function MyFormula(Column As String) As String

    Dim Col As Long
    Dim MyCell As String

    With ThisWorkbook.Sheets("TRT RTI Challenges")
        Col = .Rows(1).Find("Colors").Column
        MyCell = Left(.Cells(2, Col).Address, 2) & 2
        MyFormula = "=ET(" & MyCell & "=""Bleu"")"
    End With

End Function

And while you are here, you should check this about how to avoid using select

Select a cell within a formula with its position related to its column name in a data table

1 Answers1