Coloring cells in a sheet based on conditional formatting in other sheet.

[Kenimiro]

Hello,

I have a sheet containing different itemized lists of set items. This is automatically color coded based on data validation combined with conditional formatting. There are three separate tabs here (Necro 1, Necro 2, Barb 1). Each tab has 2 lists ie. Necro1 A and Necro1B

These sheets then output to a consolidated list showing all of the elements in the other lists in this fashion:
Rows:
Necro1A
Necro1B
Necro2A
etc.

Columns: Item 1, Item 2, Item 3 etc.

The issue I am having is taking the cell color (from conditional formatting) on sheets 1-3 and having it output on sheet 4.

I have tried to resolve this using vba but can't get it to work using Interior.Color

The only method that has worked is incredibly tedious. It applies the same conditional formatting to each cell (as they are all unique) as in the original sheets by checking the value of the original cell vs the drop down list values.

This results in 4 separate formulas per cell for 6 lists with 16 items. Can anyone help me simplify this so I don't have to do conditional formatting 384 times?

I have attached the file, you can see the conditional formatting solution approach I took in the Keep sheet A2 and B2. If input is changed in Necro 1 C2 and C3 the color does change. Just hoping for an easier method.

[Andy Pope]

You need to use the DisplayFormat.Interior.Color from the source cell and apply it to the Interior.Color of the cell to be changed.

[Croweater]

Not sure...but I think your problem is that when you use conditional formatting to change the colour appearance of the cell, it doesn't update the interior colour.

you may have to copy the cell and use a paste format?

[Kenimiro]

You need to use the DisplayFormat.Interior.Color from the source cell and apply it to the Interior.Color of the cell to be changed.

Hi Andy,

Thanks for the reply, my issue is that as Croweater mentioned it doesn't appear to log Interior.Color when it is done via conditional formatting. I appreciate the thought though and any other tips you may have

[Kenimiro]

Hi Croweater,

Thanks, thats probably what I'll end up doing is pasting the format and just changing the cell information. It's tedious... but it works!

[Croweater]

It needn't be tedious if you do it in VBA. Let me know if you need help with that.

[Andy Pope]

DisplayFormat is the property to use for obtaining the cell colour, even if CF is applied.

In you code, which I could not make head or tail of, you where setting the DisplayFormat.Interior.Color property. This is wrong.
You should be reading that property and then applying it to the Interior.Color of the cell being updated.