MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to select 1 cell and the cell above when condition=true


Posted by Mac Porter on May 11, 2000 7:18 PM

    A     B     C   

1    JAN   FEB   MAR   

2    200   300   200   
          
              
    Sub macro1()          
    '          
        For Each c In Range("A2:C2")          
        If c.Value = 200 Then          
        c.Font.ColorIndex = 3  'makes font color red   
        ElseIf c.Value <> 200 Then          
        c.Formula = c.Formula          
        End If          
        Next c          
    End Sub          
   
I do not know how to write the code to get the following result: when c.value=200 to have colored the cell that have 200 (A2,C2) and the cell above it also (JAN,MAR) (A1,C1).

Thank you so much for your help.


Posted by Ivan Moala on May 11, 2000 11:37 PM

   


Try putting in

c.Offset(-1,0).Font.ColorIndex = 3 'makes font color red so the full macro should be;

Sub macro1()
'
For Each c In Range("A2:C2")
If c.Value = 200 Then
c.Offset(-1,0).Font.ColorIndex = 3
c.Font.ColorIndex = 3 'makes font color red
ElseIf c.Value <> 200 Then
c.Formula = c.Formula
End If
Next c
End Sub

Ivan