Conditional Formatting and Data Validation.

[LutonBarry]

Folks,

Not sure if this is possible at all. On the spreadsheet attached I have a data validation list named 'Status" that refers to the items in Cells B19:B29, these cells are all individually formatted.

I have Data Validation applied to the cells in rows 3 to 15 and what I want to happen is when for example "WFH" is selected from the Data validation list then the formatting applied in cell B24 is applied and if "Training" then the format in cell B23 etc.

I've looked around but I've not found anything conclusive.

I'm using Excel 2013.

Thanks in advance.

Regs Barry

[mike7952]

I dont know if this can be done with just a formula. Would need a Macro/VBA code. See attatched workbook.

[LutonBarry]

Hello Mike and thanks so much for having a look and spending some time to help me.

I tired using the book but I get a 438 error and the debug highlights this line

iColor(i - 19) = Range("B" & i).DisplayFormat.Interior.Color

Would you have any ideas why.

Thanks.

[LutonBarry]

Mike,

Thanks very much I was running the file at home on Excel 2007. I've tried today at work on 2013 and it works a treat. Having looked into the error I need to download some patches but it works for the location I am working in currently/

[LutonBarry]

Not sure if Mike is around but he provided a solution to an issue I had that worked great. I now need to extend the Data Validation criteria and have edited the code in a way I thought would work but it fails at this line
i = Application.Match(Target.Value, Range("B23:B37"), 0)

I've no idea why as I believe I have followed the logic within the code. Also would it be possible to delete the criteria in cells A22:B22 for it to still work?

[mike7952]

Just need to Redim iColor. I'm not sure what you mean by deleting A22:B22? But see if this takes care of it.

From

ReDim iColor(10)

to

ReDim iColor(15)

[LutonBarry]

Mike,

Apologies for the late reply. hanks very much as you know I completely missed the Redim. I'm learning albeit slowly.

Thanks again.

Barry