Attribute value based on shading colour???

[Jasesair]

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.

 

[JoeMo]

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
	C	D	E	F	G	H	I	J	K
8						2	2	2	3
9						3	3	2	1
10						1	1	1	2
11						2		2	2
12						3	2	1
13						2	1	2	3
14						3	1	3	1
15						3	3	1	1
16						3	2	2	3
17						2	1	2	2
18							3	1	2
19						3		2	3
20						3	1	3	3
Sheet1

Cell Formulas
Range	Formula
H8:K20	H8	=WhatColorFill(C8)

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]

This looks perfect!! I've set the colours as RGB colours. Would this be an easy adjustment to make? Appreciate your help.

 

[JoeMo]

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?

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

 

[Jasesair]

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.

 

[JoeMo]

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]

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]

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.

Sub Button1_Click()
'Forms control command button placed on the sheet where colors are changed
ActiveSheet.Calculate
End Sub

 

[Jasesair]

So good! Really appreciate your patience and expertise!

 

[JoeMo]

So good! Really appreciate your patience and expertise!

You are welcome - thanks for the reply.