Attribute value based on shading colour???

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
223
Office Version
  1. 2016
Does anyone know how to attribute a value based on a shading colour? I have cells C8:F20 and would like to attribute a value to each of these cells when they are either shaded 'good green', 'bad red' or 'neutral yellow' (the conditional format colours). The attributed values can be recorded in corresponding cells H8:K20. Any help would be great.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You didn't set the values you want. I used 1 for good green, 2 for bad red and 3 for neutral yellow in the example below. Unfilled cells or cells filled with any other color get a null value. The example uses a UDF shown below the example.
Book1
CDEFGHIJK
82223
93321
101112
112 22
12321 
132123
143131
153311
163223
172122
18 312
193 23
203133
Sheet1
Cell Formulas
RangeFormula
H8:K20H8=WhatColorFill(C8)

VBA Code:
Function WhatColorFill(R As Range) As Variant
Application.Volatile
    If Not R.Interior.Color = xlNone Then
        Select Case R.Interior.Color
            Case 13561798: WhatColorFill = 1
            Case 13551615: WhatColorFill = 2
            Case 10284031: WhatColorFill = 3
            Case Else:     WhatColorFill = ""
        End Select
    End If
End Function
 

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
223
Office Version
  1. 2016
This looks perfect!! I've set the colours as RGB colours. Would this be an easy adjustment to make? Appreciate your help.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This looks perfect!! I've set the colours as RGB colours. Would this be an easy adjustment to make? Appreciate your help.
Easy is a relative term, but how's this?
VBA Code:
Function WhatColorFill(R As Range) As Variant
Application.Volatile
    If Not R.Interior.Color = xlNone Then
        Select Case R.Interior.Color
            Case RGB(198, 239, 206): WhatColorFill = 1
            Case RGB(255, 199, 206): WhatColorFill = 2
            Case RGB(255, 235, 156): WhatColorFill = 3
            Case Else:     WhatColorFill = ""
        End Select
    End If
End Function
 
Solution

Jasesair

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

ADVERTISEMENT

This works perfectly the first time but when I change the colours, it doesn't update. I've only been able to get it to update by dragging the formula down and across...and have to do this on every subsequent occasion any shading takes place. Not sure if it matters, but the shading is happening with a double click event.

Ideally, if I could assign this macro to a button (just a self created shape), that would be perfect. I'm a bit new to VBA and it doesn't look like this function can be assigned as a macro.
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This works perfectly the first time but when I change the colours, it doesn't update. I've only been able to get it to update by dragging the formula down and across...and have to do this on every subsequent occasion any shading takes place. Not sure if it matters, but the shading is happening with a double click event.

Ideally, if I could assign this macro to a button (just a self created shape), that would be perfect. I'm a bit new to VBA and it doesn't look like this function can be assigned as a macro.
Rather than a macro button,try pressing the F9 key on your keyboard to update the function after you change colors. This causes the sheet to calculate. Anytime the sheet calculates the UDF will update in whatever cells it is in.

Alternatively, you can add a sheet calculation to your double-click event code to cause the UDF to update. Post that event code if you need help with that.
 

Jasesair

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

ADVERTISEMENT

I'm wanting a button anyway for the user. They will play with their colour shading selections and once satisfied will hit a button for this to 'save' these selections (which are your numbers attributed to shading). They will be saved elsewhere in the workbook so they can be later retrieved. So, anything that can be attributed to a macro button would be awesome. At a stretch, perhaps the macro button will trigger F9???
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You can use a single line at the start of your button that saves the selections. This one assumes the button is on the sheet with the colors to be saved. If not, replace ActiveSheet with Sheets("Sheet name") where colors reside.
VBA Code:
Sub Button1_Click()
'Forms control command button placed on the sheet where colors are changed
ActiveSheet.Calculate
End Sub
 

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
223
Office Version
  1. 2016
So good! Really appreciate your patience and expertise!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,099
Messages
5,628,679
Members
416,332
Latest member
blkbeltmjk

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