Conditional formatting based on merged cells (or using VBA based on cell colour)

Lost_in_Excel

New Member
Joined
Oct 22, 2010
Messages
18
Hi. I have a very, large dataset that I download each month which includes monthly figures, actual and forecast spend, for the year. A portion of the download is shown in the image
View attachment 43078
The only ways to distinguish which months represent actuals are that they are shaded and that the header on Row 1 for those months is merged. For example in the image Cells B1 to D1 is merged and next month Cells B1 to E1 will be merged and Cells B3 to E3 will be shaded. I separate out the data I require from the data set using vlookups, xlookups, etc but I need to shade the actuals to mimic the shading in the original dataset.

Is there anyway to use conditional formatting to shade Cells B3 to D3 if Cells B1 to D1 are merged. Alternatively I have tried setting up VBA macros in each ThisWorksheet module to color the cells based on the shading in the original download. Below is a snippet of my code. It seems to work fine when the sheet is unprotected, however, it throws up a "Application defined or object defined error" dialogue box if I click on a cell when the sheet is protected. I am using the UserInterFaceOnly method to run macros in protected sheets.

VBA Code:
If ThisWorkbook.Worksheets("Download").Range("b3").Interior.ColorIndex = 48 Then
    ThisWorkbook.Worksheets("Sheet1").Range("b3:b10").Interior.ColorIndex = 48
    Else: ThisWorkbook.Worksheets("Sheet1").Range("b3:b10").Interior.ColorIndex = xlColorIndexNone
End If

Ideally I'd prefer to used conditional formatting as I have many sheets that will each require their own tailored macro and my knowledge of VBA is mostly from Googling and forum boards and playing with what I find.
Many thanks for any help.
 

Attachments

  • 1626720756387.png
    1626720756387.png
    9.9 KB · Views: 8

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,147,635
Messages
5,742,244
Members
423,717
Latest member
rubthenut

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