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

  • Thread starter Thread starter Chris Maddogz
  • Start date Start date
C

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.
 
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
 
Thanks again Jacob works fine

Jacob Skaria said:
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
Click to expand...
 
Back
Top