열 이름을 범위로 사용

Question

VBA에서 열 이름을 범위로 사용하는 방법 조건부 서식에 아래 코드(CTTO)를 사용하지만 범위를 수동으로 표시했습니다. 이번에는 조건부 서식을 적용하려는 테이블이 여러 개 있으므로 열 이름을 사용해야 합니다.

    'Definining the variables:
  Dim rng As Range
  Dim condition1 As FormatCondition, condition2 As FormatCondition, condition3 As FormatCondition


 'Fixing/Setting the range on which conditional formatting is to be desired
  Set rng = Range("M5", "M16")


  'To delete/clear any existing conditional formatting from the range
   rng.FormatConditions.Delete


  'Defining and setting the criteria for each conditional format
   Set condition1 = rng.FormatConditions.Add(xlCellValue, xlEqual, "=""""")
   Set condition2 = rng.FormatConditions.Add(xlCellValue, xlGreater, "=report2!$K$1")
   Set condition3 = rng.FormatConditions.Add(xlCellValue, xlLess, "=report2!$K$1")

   rng.FormatConditions(1).StopIfTrue = True
   rng.FormatConditions(1).SetFirstPriority
   rng.FormatConditions(2).StopIfTrue = False
   rng.FormatConditions(3).StopIfTrue = False

   'Defining and setting the format to be applied for each condition
   With condition2
    .Font.Color = -16383844
    .Font.Bold = True
    .Font.TintAndShade = 0
    .Interior.PatternColorIndex = xlAutomatic
    .Interior.Color = 13551615
    .Interior.TintAndShade = 0
   End With

   With condition3
    .Font.ThemeColor = xlThemeColorDark1
    .Font.Bold = True
    .Font.TintAndShade = 0
    .Interior.PatternColorIndex = xlAutomatic
    .Interior.ThemeColor = xlThemeColorAccent6
    .Interior.TintAndShade = -0.499984740745262
   End With

감사합니다