Macro assistance requiered

chubbers001

New Member
Joined
Nov 4, 2011
Messages
17
I am trying to write a macro that will change the colour fill in a range of cells b11:d13 depending on the colour fill in cells b14:d16. If the colour fill in range b14:d16 is red (or colour 255) then the colour fill in the range b11:b13is to be red (or colour 255), but if the colour fill in range b14:d16 is green (or colour 5287936) then the colour fill in the range b11:d13 is to be green (or colour 5287936). I have tried to write the macro (I am a novice at this) and here is my effort,

Sub CBEarthOFFBC6B()
'
' CBEarthOFFBC6B Macro
'

'
If Range("B14:D16") = Color = 255 Then
Range("B11:D13").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
If Range("B14:D16") = Color = 5287936 Then
Range("B11:D13").Select
ActiveSheet.Unprotect
Range("B11:D13").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B9").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End If
End Sub
Any assistance would be appreciated thanks
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Try something like:
Code:
Sub CBEarthOFFBC6B()

    With Range("B11:D13")
        .Interior.Color = Range("B14").Interior.Color   'checks the color from just one cell and uses the same color
    End With

End Sub
 
Upvote 0
How are cells b14:d16 colour filled? Cells that are coloured with Conditional Formatting are not the same as cells that are filled manually. Manual background colour and Conditional Formatting may "look" the same but they are not.

If they are coloured filled using conditional formatting, you could use the same conditional formatting formulas to fill cells b11:d13.
 
Upvote 0
Thanks Misca
I have just tried your sugestion and it works well, thanks for your help, I have been pulling my hair ou ton this for about 7 days and you fixed it in 2 min.
Thanks again.
 
Upvote 0

Forum statistics

Threads
1,207,283
Messages
6,077,518
Members
446,287
Latest member
tjverdugo85

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
Back
Top