excel macro If and and and or then

sarics
sarics used Ask the Experts™
on
Hello expert,
please can you help me

If C column have 0000 and D column=1 and E column=2 and A column 3 or 5 then sum B column +18 and then result put in C column and color yellow

If C column have 0000 and D column=1 and E column=2 and A column 4 or 6 then sum B column +21 and if sum B column+21=<2006 put 2006 in C column and if is not put result in C column and color green.

thx.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
A sample workbook with desired output would be helpful.

Author

Commented:
Top Expert 2016
Commented:
Hi,

pls try

Sub macro()

For Each c In Range(Range("C2"), Range("C" & Cells.Rows.Count).End(xlUp))
    If c = 0 And c.Offset(0, 1) = 1 And c.Offset(0, 2) = 2 Then
        If c.Offset(0, -2) = 3 Or c.Offset(0, -2) = 5 Then
            c.Value = c.Offset(0, -1) + 18
            c.Interior.Color = vbYellow
        ElseIf c.Offset(0, -2) = 4 Or c.Offset(0, -2) = 6 Then
            c.Value = Application.Max(2006, c.Offset(0, -1) + 21)
            c.Interior.Color = RGB(146, 208, 80)
        End If
    End If
Next
End Sub

Open in new window

Regards

Author

Commented:
Thank you, you 're a magician :-) work perfectly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial