VBA - Highlight cell if greater or less than in dynamic range

[Maleko-1]

Hi there,
I can't find an answer to this in VBA.

Cell D5 has a value (upper limit) and cell D6 has a value (lower limit).
From D10 down to the end of data in column D are values that if falls outside of the upper and lower limit above I need the cell highlighted red.
Another catch is...I need the same scenario to happen in all other columns to the right that has data.
For example, E5 and E6 (upper and lower limits) and E10 down has the data and so on to the last column that has data.

If anyone can help me with this it would be so appreciated and I would forever be in debt to you.

Thank you
Marc

 

[BarryL]

if the range was D10:X33 it would look like

Sub Macro1()
    Range("D10:X33").FormatConditions.Add Type:=xlExpression, Formula1:="=OR(D10<D$6,D10> D$5)"
    Range("D10:X33").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Range("D10:X33").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
End Sub

just change the range to suit you

 

[BarryL]

for some reason the less than/greater than symbols won't work on this sheet. Formula1:= should equal the below

"=OR(D10 less than symbol D$6,D10 greater than symbol D$5)"

 

[Maleko-1]

The range will always be changing.
Also, is that formula referring to D5 and D6 for the entire range? I need to refer to rows 5 and 6 in each column as these values will be different for the data in each column from row 10 down.

 

[BarryL]

yes but that's why they have the $$ signs so as the spread across they will change to E5/E^ etc but always on the 5/6 row. all you need to change is the range. I have no other information to go with

 

[Maleko-1]

Thanks BarryL for your help. I will try to work with this to see if I can get the results I need.

I appreciate your time and help with this.

 

[Maleko-1]

Okay, thanks to your help it is doing what I wanted.
The only issue is that I have to make the range an exaggerated size so that it will cover any possible number of rows and columns. This leaves a lot of highlighted empty rows beneath the last row of data.

Is there a way to use your code but make it stop highlighting after the last row with data?

Thanks