If cell contains specific colour shading then...

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
219
Office Version
  1. 2016
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!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,117
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 8, 2015
Messages
219
Office Version
  1. 2016
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,117
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 8, 2015
Messages
219
Office Version
  1. 2016

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,117
Office Version
  1. 365
Platform
  1. Windows
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:

ABCD
1
2Good80
3OK55
4
5
6IDScore1Score2Score3
7A253060
8B706570
9C808582
10D827868
11E505560
12F856050
13G605072
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:D13Expression=B7>=GOODtextNO
B7:D13Expression=B7>=OKtextNO

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

Board Regular
Joined
Apr 8, 2015
Messages
219
Office Version
  1. 2016
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,972
Members
416,002
Latest member
Neshx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top