Lost_in_Excel
New Member
- Joined
- Oct 22, 2010
- Messages
- 20
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.
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.
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 IfIdeally 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.
