arr = another sheet

[handysmurf]

I need this to reference sheet "Formatting" so I don't have to keep updating the cell references when I edit the sheet.

arr = Array("$BA:$BA", "BA2", "$BB:$BB", "BB2", "$BC:$BC", "BC2", "$BD:$BD", "BD2", "$BE:$BE", "BE2", "$BF:$BF", "BF2", "$BG:$BG", "BG2")

 

[Scott Huish]

You didn't say what you were doing with those ranges exactly, but you could just reference it with something like this:

Sub test()
arr = Array("$BA:$BA", "BA2", "$BB:$BB", "BB2", "$BC:$BC", "BC2", "$BD:$BD", "BD2", "$BE:$BE", "BE2", "$BF:$BF", "BF2", "$BG:$BG", "BG2")
Worksheets("Formatting").Range(arr(0)) = 1
End Sub

That would put the number 1 in the first range in that array, but change it to do whatever you need.

 

[handysmurf]

That is not the complete code ... do you need to see it all?


the array contains the background color for conditional formatting as well as the names associated with that color

 

[handysmurf]

Sub conditional_formatting()

 

 Application.Run "Links"

  Dim lr As Long, i As Long
  Dim arr As Variant
 
  Sheets("Day2Day").Select
  Cells.FormatConditions.Delete
  lr = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  arr = Array("$A:$A", "A2", "$B:$B", "B2", "$C:$C", "C2", "$D:$D", "D2", "$E:$E", "E2", "$F:$F", "F2", "$G:$G", "G2")
 
  With Range("A3:p" & lr)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$k3=""Parenting"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
      .PatternColorIndex = xlAutomatic
      .ThemeColor = xlThemeColorAccent2
      .TintAndShade = 0.599963377788629
    End With
    With .FormatConditions(1).Font
      .Bold = True
      .Color = -16776961
    End With
    With .FormatConditions(1).Borders(xlTop)
      .LineStyle = xlContinuous
      .TintAndShade = 0
      .Weight = xlThin
    End With
    .FormatConditions(1).StopIfTrue = False
  End With
  
 With Range("q3:V" & lr)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$K3=""Parenting"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
      .PatternColorIndex = xlAutomatic
      .ThemeColor = xlThemeColorAccent4
      .TintAndShade = 0.599963377788629
    End With
    With .FormatConditions(1).Font
      .Bold = True
      .Color = -16776961
    End With
    .FormatConditions(1).StopIfTrue = False
  End With
 
  With Range("W3:AB" & lr)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$k3=""Parenting"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
      .PatternColorIndex = xlAutomatic
      .ThemeColor = xlThemeColorAccent6
      .TintAndShade = 0.599963377788629
    End With
    With .FormatConditions(1).Font
      .Bold = True
      .Color = -16776961
    End With
    .FormatConditions(1).StopIfTrue = False
  End With
 
  With Range("A3:n" & lr)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($k3<>""Parenting"",$A3=$A4,$j3<>$j4)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    .FormatConditions(1).StopIfTrue = True
  End With
  
   With Range("A3:n" & lr)
    For i = 0 To UBound(arr) Step 2
      .FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(" & arr(i) & ",$m3)"
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      .FormatConditions(1).Interior.Color = Range(arr(i + 1)).Interior.Color
      .FormatConditions(1).StopIfTrue = False
    Next
  End With
  
  Application.Run "Autofit"
  Application.Run "FormulaReset"
  Application.Run "drivelink"
  Application.Run "HighlightKeyWords"
  Application.Run "highlightnumbers"

  
End Sub