Unlock Cells With Certain Color

[pezalmendra]

Hi!

Does someone know how to, via a VBA code, unlock all the cells from my workbook that has Interior.Color = 10092543

Thanks!!

Kind regards,

pezal

[ragulduy]

Please Login or Register  to view this content.

[stnkynts]

I think you may have forgot to add the part about unlocking the cell. Amended your code

Please Login or Register  to view this content.

[ragulduy]

Cheers, think I forgot more than that!

[pezalmendra]

Hello yudlugar,

Thanks for the code! I finally adapted this way:

Please Login or Register  to view this content.

The code is running okay but I discovered that those cells which have been edited via a conditional formatting, wont unlock using this code. Seems like the Interior.Color = 10092543 does not correspond to that one that the conditional formatting is applying... could that be correct? Is there a way to check which fill color does a cell have?

Thank you very much.

Regards,

pezal

[ragulduy]

You are correct, conditional formatting doesn't change the interior.color property.

[pezalmendra]

Hi stnkynts,

I have just seen your code, thanks!

Your code runs perfect but I discovered that all cells that were colored via a conditional formating wont unlock using the vba code... I think that is maybe because the fill color from those cells is not exactly the same as put in the macro. The thing is that I dont know how to check what the fill color from those cell is.

Regards

[pezalmendra]

Yes but I would like to just unlock the cells, not to change the interior color...

[stnkynts]

@pezalmendra. What yudlugar is saying, is that if you change the color of a cell using conditional formatting a macro will not be able to recognize that the cell has changed color. If this is a must, it would be best to have a macro color the cells or find a different parameter to apply the "unlock" macro to.

[ragulduy]

You can pick up the colour of conditional formatting from:

Please Login or Register  to view this content.

and you can get the formula for the conditional formatting from:

Please Login or Register  to view this content.

So in principle, you could test whether the format condition is true and then pick up the appropriate colorindex.

Note - I've not actually done this before, I'm guessing there is an easier way of doing this but this was just what sprung to mind.

[ragulduy]

Maybe take a look here:
http://www.cpearson.com/Excel/CFColors.htm