Applying conditional formatting to merged cells

[PEH169]

Hi, This is my first post, so sorry if it is not structured well.

I have data that I have exported from Power BI and will not allow for the conditional formatting applied to be reflected in excel. My data has significant repetition in the Rows as the data has a hierarchal structure with entry # id's (e.g. record 1, 2, 3) having 'sub rows' as the record may have multiple sub data associated to it (each record has a different number of sub data rows). The result is that I have (for example) 4 rows containing record id 1, 2 rows with record id 2 and 5 rows with record id 3.

I have run a VBA macro to merge, centre and align the id's so that only the sub data rows are separated. I have applied the formatting to each of the rows that are only related to the id level and not the sublevel.

Every time I try and record a macro the related to the conditional format of the text in a box (e.g. Red / Amber / Green) to colour the cell in the appropriate colour across these merged cells in columns, the macro does not deliver the outcome (but longhand typing in the conditional formatting does, until I copy new data in and try and run the macro)

Is there a simple VBA script that I could run as a macro that would check the merged rows in the column; identify if the text is red / amber / green; then move on to the next set of merged cells and do the same again until the end of the data?

I have spent all day pulling my hair out, so any advice would be greatly received!

Thanks!
Paul

 

[Joe4]

Welcome to the Board!

Merged cells are very, very bad, and should really never be used in Excel (they are probably Excel's worst feature!).
They cause nothing but trouble for things like VBA, sorting, and many other things, and should be avoided if at all possible!

As long as you are using VBA to format the data, I would recommend having it re-format the data into a more usable format that doesn't used merged cells.
It may be a little more work, but should pay off in the long run.

Note: There is a "Center Across Selection" formatting option that gives you the same visual effect as merged cells for horizontal merging (see here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis). Unfortunately, there is no equivalent option for vertical merging.