Filling a Cell with Yellow backgound after testing for -ve cell d

[Chris Maddogz]

In my worksheet "Major MACD Sep'09" I have calculated positive or negative
price variance data (format +/- $$.cc) from Row 5 down in each alternate
Column from C through BI (ie Column C,E,G,I etc to BI).
After the latest entry in any Column there is always the value 0.00.
I need to find any criteria where the last three price variation entries
before the 0.00 value are all negative and if this test is met I need to fill
Row 3 of that particular Column with a yellow background in order to
highlight the situation.

 

[Jacob Skaria]

Hi Chris


Try the below..which will work on the active sheet. The below will check
only row3...Adjust to suit..

Sub Macro()
Dim lngCol As Long, intCount As Integer
Range("B3").Interior.ColorIndex = xlNone
For lngCol = Range("BI3").Column To Range("C3").Column Step -2
If Cells(3, lngCol) < 0 Then intCount = intCount + 1 Else intCount = 0
If intCount = 3 Then Range("B3").Interior.ColorIndex = 6: Exit For
Next
End Sub

If this post helps click Yes

 

[Chris Maddogz]

Thanks again Jacob works fine

Hi Chris


Try the below..which will work on the active sheet. The below will check
only row3...Adjust to suit..

Sub Macro()
Dim lngCol As Long, intCount As Integer
Range("B3").Interior.ColorIndex = xlNone
For lngCol = Range("BI3").Column To Range("C3").Column Step -2
If Cells(3, lngCol) < 0 Then intCount = intCount + 1 Else intCount = 0
If intCount = 3 Then Range("B3").Interior.ColorIndex = 6: Exit For
Next
End Sub

If this post helps click Yes