Color coding cells based on value through a column with specific header

Question

I have this code in vba that defines arrays for column headers that I want copy/pasted in another tab in Excel. However, in one of the new tabs, I also want to color code some cells based on their value in the column "BOM PROCESS TYPE (A, U, R, D)" which corresponds to position 2 in that array. The code runs without giving me an error, but the cells don't change color at all. Skipping some parts, this is what I have, does anyone know how to fix it?

'My variables.

Dim i As Long, rngCell As Range, rCell As Range
Dim c As Long, v As Long, vMHDRs As Variant, vBHDRs As Variant
Dim s As Long, vNWSs As Variant, wsMM As Worksheet

vBHDRs = Array("BOM LEVEL", "BOM PROCESS TYPE (A, U, R, D)", "ALTERNATIVE ITEM: GROUP")

'Skipping most of the code and jumping to the color coding section:

With Sheets("BOM")
v = 2
Set rngCell = Sheets("BOM").UsedRange.Find(What:=vBHDRs(v), LookAt:=xlWhole)   
If Not rngCell Is Nothing Then        
Set rngCell = Intersect(Sheets("BOM").UsedRange, rngCell.EntireColumn)
    For Each rCell In rngCell
    If rCell.Value = "D" Then rCell.Interior.ColorIndex = 3
    If rCell.Value = "R" Then rCell.Interior.ColorIndex = 6
    If rCell.Value = "U" Then rCell.Interior.ColorIndex = 6
    Next
End If
End With

Any thoughts?

Answer 1

I have just simulated your colouring code and got it working. I believe your problem is the v=2 line This is because of the way you have allocated your array and the default excel settings. The lower boundary for an array when allocated using your method above is 0, so this means you v=2 is refering to the "ALTERNATIVE ITEM: GROUP" column and so it is not finding D,R or U in that column. You can either change to V=1 (and this works) or set option base 1 at the top of your module as this will change the default lower boundary to 1. I actually advise against the option base 1 if you have multiple modules as if you forget to put option base 1 at the top of all of them, you might get unexpected results. As mentioned above, you dont need the Sheets("BOM") inside yoru With block, but it doesn't affect it working. This is the very slightly amended code that works for me

Sub test2()
Dim i As Long, rngCell As Range, rCell As Range
Dim c As Long, v As Long, vMHDRs As Variant, vBHDRs As Variant
Dim s As Long, vNWSs As Variant, wsMM As Worksheet
vBHDRs = Array("BOM LEVEL", "BOM PROCESS TYPE (A, U, R, D)", "ALTERNATIVE ITEM: GROUP")
With Sheets("BOM")
    v = 1
    Set rngCell = Sheets("BOM").UsedRange.Find(What:=vBHDRs(v), LookAt:=xlWhole)
    If Not rngCell Is Nothing Then
    Set rngCell = Intersect(Sheets("BOM").UsedRange, rngCell.EntireColumn)
        For Each rCell In rngCell
        If rCell.Value = "D" Then rCell.Interior.ColorIndex = 3
        If rCell.Value = "R" Then rCell.Interior.ColorIndex = 6
        If rCell.Value = "U" Then rCell.Interior.ColorIndex = 6
        Next
    End If
End With
End Sub

Answer 2

When you use With construction, you shouldn't use Sheets("BOM"), should you?

Set rngCell = .UsedRange.Find(What:=vBHDRs(v), LookAt:=xlWhole)  

Answer 3

If your target sheets and the logic around the colouring of cells is consistent, then could you not achieve your desired objective using conditional formatting on the target sheets cells. Then all you macro needs to do is the copying.