Macro to have multiple if argument

chubbers001

New Member
Joined
Nov 4, 2011
Messages
17
I am trying to write a macro that has multiple if arguments.
An example is as follows,
If cell b11 had fill colour red, then colour fill cell B8 green,
If cell b11 had fill colour green, then colour fill cell B8 red,
If cell b11 had fill colour yellow, then colour fill cell B8 blue,
If cell b11 had fill colour blue, then colour fill cell B8 yellow,
end if.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What did you actually try, if anything? One possible syntax is:

Code:
If condition1 Then
'Code if True
ElseIf condition2 Then
'Code if True
ElseIf condition3 Then
'Code if True
ElseIf condition4 Then
'Code if True
End If
 

chubbers001

New Member
Joined
Nov 4, 2011
Messages
17
This is wat I have tried, I am a novice at this I have just looked at some other spread sheets macros and tried to adapt and alter to what I think but that doesnt seem to be enough.

Sub CBEarthOFFBC6B()
'
' CBEarthOFFBC6B Macro
'

'
ActiveSheet.Unprotect
if range ("b11:d13") =With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255 'red
.TintAndShade = 0
.PatternTintAndShade = 0 then
Range("B8:D10").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936 'green
.TintAndShade = 0
.PatternTintAndShade = 0
if range ("b11:d13") =With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 3932145 'yellow
.TintAndShade = 0 then
Range("B8:D10").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696 'blue
.TintAndShade = 0
.PatternTintAndShade = 0

End With

End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
More like:

Code:
Sub CBEarthOFFBC6B()
'
' CBEarthOFFBC6B Macro
'
'
    ActiveSheet.Unprotect
    With Range("B11:D13")
        If .Interior.Color = 255 Then 'red
            With Range("B8:D10").Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 5287936 'green
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        ElseIf .Interior.Color = 3932145 Then  'yellow
            With Range("B8:D10").Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 15773696 'blue
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    End With
End Sub
 

tlowry

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
Whenever I’m using multiple if elseif statements, I try and use a select case statement(s). Below is an example of how I’d code this exercise.

I seems to be easier to understand for me and I’m less likely to make a logic mistake.

By the way, I used ColorIndex instead of PatternColorIndex
‘---------------------------------------------
‘---------------------------------------------
‘---------------------------------------------
Option Explicit
Sub ChangeColor()
Dim ired As Integer, igreen As Integer
Dim iyellow As Integer, iblue As Integer
ired = 3
igreen = 4
iyellow = 6
iblue = 5
With Range("B8").Interior
Select Case Range("B11").Interior.ColorIndex
Case ired
.ColorIndex = igreen
Case igreen
.ColorIndex = ired
Case iyellow
.ColorIndex = iblue
Case iblue
.ColorIndex = iyellow
End Select
End With
End Sub
‘---------------------------------------------
‘---------------------------------------------
‘---------------------------------------------
 

chubbers001

New Member
Joined
Nov 4, 2011
Messages
17
More like:

Thanks Andrew I used your code and it works well.

tlowry, I tried to use your method but I didnt really understand how to implement the code into a macro, sorry.
I would like to try your method if you could tell me how to use the code you wrote and place it into a macro.

Thanks to all for the replies.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,331
Messages
5,595,549
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top