Range of columns change colors based on cell column value

[Kleets]

This may be a simple solution and I may be overthinking it...

I have a column that contains hours devoted to a project and another column that sates the maximum hours spent on the project. The goal is to high light red when the devoted hours have exceeded the set maximum hours. Currently I have the following code.

Sub HighlightOT()

Dim rng As Range

For Each rng In Worksheets(2).Range("D:D")
If rng.Value > 120 Then
rng.Interior.Color = vbRed
End If

Next rng

For Each rng In Worksheets(3).Range("D:D")
If rng.Value > 120 Then
rng.Interior.Color = vbRed
End If

Next rng

For Each rng In Worksheets(4).Range("D:D")
If rng.Value > 120 Then
rng.Interior.Color = vbRed
End If

Next rng

For Each rng In Worksheets(5).Range("D:D")
If rng.Value > 120 Then
rng.Interior.Color = vbRed
End If

Next rng


End Sub

The macro is across four sheets and each sheet has column D as devoted hours and if the hours exceed 120 high light red. What I want is to replace 120hrs with a column on each sheet that I can cross check to column D. Not all rows will have 120hrs they will vary.

if someone can help that would be great!

 

[Joe4]

Welcome to the Board!

Why are you trying to use VBA to do this?
This can be done very easily with Conditional Formatting, which is dynamic and requires no VBA.

 

[Kleets]

Welcome to the Board!

Why are you trying to use VBA to do this?
This can be done very easily with Conditional Formatting, which is dynamic and requires no VBA.

Agreed it is simpliar in conditional formatting but in this case it must be done in VBA...

 

[Joe4]

Agreed it is simpliar in conditional formatting but in this case it must be done in VBA...

Why?

If there are certain underlying reasons why, that might be important to any solution we do via VBA.

Also note a few other points:
1. You can perform/create Conditional Formatting rules using VBA. The Macro Recorder will get you a lot of the code you need for that.
2. Loops are notoriously slow and inefficient. They should be avoided whenever possible, if there are better options. Loop through over 1 million rows on 5 sheets is generally not a good idea. At the very least, you should find the last row with data in column D on each sheet, and only go down as far as that.