VBA Highlight consequent cells based on value from another cell with criteria

[Bluefox]

Hello together,

please help me to extend my VBA code to highlight amount of cells (top to bottom) for a certain year based on how many tools are planned in separate table on the right side in attached "example".
So far i wrote code that only counts tools for first country, but i dont know how to bind it to value from another table.

For example in USA it is planned to have 2 cutting tools in 2023 so for 2 cutting tools must be highlighted 2 cells in col. C
thank you in advance, i am totally new to vba.


Dim rng1, rng2 As Range
Dim criteria1, criteria2 As Variant
Dim result As Double

Set rng1 = Range("A2:A14")
criteria1 = "USA"

Set rng2 = Range("B2:B14")
criteria2 = "Cutting tool"

result = WorksheetFunction.CountIfs(rng1, "*" & criteria1 & "*", rng2, "*" & criteria2 & "*")

[Paul_Hossler]

Try this



Option Explicit


'assumes column headers are the same that there's enough row to hold color and that output columns are grouped
Sub HighCells()
Dim rIn As Range, rOut As Range
Dim rowIn As Long, colIn As Long, rowOut As Long, colOut As Long, N As Long


Set rIn = ActiveSheet.Range("K1").CurrentRegion
Set rOut = ActiveSheet.Range("A1").CurrentRegion

rOut.Cells(2, 3).Resize(rOut.Rows.Count - 1, rOut.Columns.Count - 2).Interior.ColorIndex = xlColorIndexNone

For rowIn = 2 To rIn.Rows.Rows.Count
For rowOut = 2 To rOut.Rows.Count

'county and tool match
If rIn.Cells(rowIn, 1).Value = rOut.Cells(rowOut, 1).Value And rIn.Cells(rowIn, 2).Value = rOut.Cells(rowOut, 2).Value Then

For colIn = 3 To rIn.Columns.Count
N = rIn.Cells(rowIn, colIn).Value
rOut.Cells(rowOut, colIn).Resize(N, 1).Interior.Color = vbYellow
Next colIn

Exit For
End If
Next rowOut
Next rowIn
End Sub

[Bluefox]

It works, MANY MANY THANKS