Excel If one cell contains a number then highlight that number of cells in a row horizontally-CodePudding

I'm looking for a way by VBA or whichever works, even 1 command button but 4-5 processes to be called

Call process1
Call process2
End Sub

on one click which somehow seems impossible for me as I'm not that advanced in Excel VBA, but hopefully this challenge is a piece of cake for others. The task is to highlight cells horizontally by range based on the formula referring to a cell in Column AM. So "B8" is an amount of 100 which needs to be divided by the number of partitions appearing on "AM3". So 100/6. Now "AM3" is 6 so starting from "C8" a number of 6 cells (Merged in 4's) will be highlighted horizontally. "C9" is relating to "AM4" which is having a value of 9 and will highlight 9 cells (Total of 36 cells since merged) horizontally .

For now this is what i have applied but it limits to only within that range :

   Sub HighlightRangeOfCells()
   Dim rng As Range
   For Each rng In Range("C8:AL12")
    If IsNumeric(rng.Value) Then
      If rng.Value <> 0 Then
        rng.Interior.Color = vbRed
     End If
   End If
   Next rng
   End Sub

Thanks for your efforts and reply whatever it may be if possible or not.

CodePudding user response：

As mentioned by

As you see, cells get highlighted when their column number in some way corresponds with the value of column "J". All you need to do is replace "J" by "AM" and describe the correspondence as you see fit.

For your information: in the formula, I'm using the reference $J1, which means that while dragging and dropping, the row number might change, but the referred column always needs to be "J" (it's a combination of absolute and relative cell references).