If cell contains specific colour shading then...

[Jasesair]

Please help wise people!

I have a grid of cells that will be shaded by the user (on double click they'll scroll through four colours to highlight as they need). I'm wanting to record this information based on the shading in separate cells. Ie. If cell was shaded 'good green' then record a '1', if cell was shaded 'middle yellow' then record a '2', if cell was shaded 'bad red' then record a '3'.

Can anyone help with a formula or similar? Appreciate any support!

 

[StephenCrump]

It sounds like you are using the Worksheet_BeforeDoubleClick event to allow the user to choose colours?

Why not use that VBA code to record 1's, 2's or 3's depending on the user's choice(s)?

It's not clear what/where you mean by "record". Can you please provide more detail?

 

[Jasesair]

I have text in cells c8:f20. With use of the double click event, the user can highlight as needed in these cells (not all cells will be highlighted). What I'm trying to achieve is to create a 'save' type button for when the user is happy with their selections. Hitting this 'save' or 'send' button will copy this information elsewhere in the workbook so it can be reproduced later by the user. This may be done about 4000 times so I'm aiming to save the data in a basic format so not to have the document's size blow out. I had thought I might be able to assign a simple number for the different highlights chosen by the user, have it save in a table (I'll end of with 4000 tables!), and reproduce it somehow. Have I lost you yet?

 

[StephenCrump]

Yes, it could be done, but I'm not sure what it would achieve. Your user is colour coding 4,000 tables, and you're creating a single backup point by duplicating the information in the workbook at that time (copying colour arrays to numeric arrays).

Wouldn't it be simpler to save backup copies as you go, i.e. for as many versions as the user might like to revert to? You could use VBA to save, prompting the user to assign a meaningful name.

I'm also curious why the user is colour coding cells good/middle/bad. Why you don't do it automatically - preferably using conditional formatting so it updates dynamically?

 

[Jasesair]

So interesting and appreciated thoughts. The end user knows nothing about Excel so it needs to feel intuitive for them. They won't be able to do conditional formatting themselves so my idea of double clicking to get the desired colour (or rating in their case) seemed reasonable. Saving does sound alright but I'm really hoping to have all information stored in the one Workbook, with the user then able to re-generate a previously saved 'table' when needed.

I'll take any advice etc.

 

[StephenCrump]

They won't be able to do conditional formatting themselves so my idea of double clicking to get the desired colour (or rating in their case) seemed reasonable.

How about you apply the conditional formatting, and simply ask the user to specify values for GOOD and OK:

	A	B	C	D
1
2	Good	80
3	OK	55
4
5
6	ID	Score1	Score2	Score3
7	A	25	30	60
8	B	70	65	70
9	C	80	85	82
10	D	82	78	68
11	E	50	55	60
12	F	85	60	50
13	G	60	50	72
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7:D13	Expression	=B7>=GOOD	text	NO
B7:D13	Expression	=B7>=OK	text	NO

To create a backup point for this scenario, you'd only need to save the two values for GOOD and OK, not the entire colour table or numerical equivalent.

 

[Jasesair]

I'd go for something like that but there is already text in each cell. It's like a marking guide to do some grading essentially. For your info also, some cells will be remain ungraded by the user.