Conditional Formatting VBA

[ststern45]

Hello everyone,

I need help with modifying the VBA code below:

I want to change the line of code below.

Select Case Right(SumDigits(CStr(a(i, j))), 1) + 0

Is it possible to create a new line of code.

For example,

In cell A1 I have the number 123

I would like to format a "custom" cell range where if a number in this custom cell range (example B1 through D10) equal 123 from cell A1, colorize the cell to the color yellow.

The rest of the code I will need to change since I just need to use 1 color and not 10.

Thanks in advance

Sub ApplyFormat()
  Dim a
  Dim i As Long, j As Long
  Dim tmp As String
  
  Application.ScreenUpdating = False
  With Range("G4:EZ108")
    .Font.Bold = False
    .Font.ColorIndex = 1
    .Interior.Color = xlNone
    a = .Value
    For i = 1 To UBound(a, 1)
      For j = 1 To UBound(a, 2)
        If Len(a(i, j)) > 0 Then
          Select Case Right(SumDigits(CStr(a(i, j))), 1) + 0
            Case 0: tmp = "255+0-255 2"
            Case 1: tmp = "255+0-0 2"
            Case 2: tmp = "255+255-0 1"
            Case 3: tmp = "0+176-240 2"
            Case 4: tmp = "255+204-0 1"
            Case 5: tmp = "255+102-0 1"
            Case 6: tmp = "128+128-128 2"
            Case 7: tmp = "153+204-0 2"
            Case 8: tmp = "0+0-255 2"
            Case 9: tmp = "0+255-0 1"
          End Select
          With .Cells(i, j)
            .Interior.Color = Evaluate(Replace(Split(tmp)(0), "-", "*256+256^2*"))
            .Font.ColorIndex = Split(tmp)(1)
            .Font.Bold = True
          End With
        End If
      Next j
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

 

[ststern45]

I wanted to reply for any help if possible.

Thanks in advance.