Change cell value based on the color of another cell

[Woodpusher147]

Hello
I hope someone can help with a (in my not super excel mind) very complicated issue.

I have the above spreadsheet to track performance which has 11 columns and however many staff there are as rows.
The sheet has conditional formating to shade each cell based on score, that's all done ok.
I also have data validation as Bedroom does not score in columns 4,5,6,7,8 - Upstairs & Downstairs do not score in 9 or 10

However, I now need to have an overall score by using the following Key
RED =0
AMBER=1
GREEN=3
YELLOW=5
The other complication is that columns 1,2,3 and 4 will score 4 times more than all other columns but I guess I can do this by having the formula/code populate hidden cell which I can then simply refer to this x4 for the cells in rows 1,2,3,4

Ive looked at an old thread which is titled "Changing cell value based on the color of another cell" and has an interesting
User Defined Function in VBA by Joe4. This could work as I dont want the code to run on all the sheet but I really need help

Rick Rothstein and sheetspread also commented and helped


I hope you can help as my head is now hurting


Thank you for any replies

Simon

 

[cmowla]

I cleared all rules then ran again and its worked fine.

Oh, so that fixed it completely? If so, just do that code line replacement I mentioned. (That line of code does that action for you too.)

 

[Woodpusher147]

Oh, so that fixed it completely?

YEs, a little strange as the cells had hard coded colour grounds after :/
I reset everything to no fill and ran the code again

All good

 

[cmowla]

YEs, a little strange as the cells had hard coded colour grounds after :/
I reset everything to no fill and ran the code again

All good

Okay, great!

Just do the suggested line of code replacement I mentioned in my edit in this post, and you will not have to ever do that again either (manually clear all rules in the sheet).

 

[Woodpusher147]

Am I right in this


if we change the multiplier of different columns, I simply change the number here
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,1,1,0},{4,4,4,4,1,1,1,1,0,0,1}))

So if Bedroom decided that Column 10 was multiplied by 4 I would simply change
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,4,1,0},{4,4,4,4,1,1,1,1,0,0,1}))

OR downstairs/UPstairs column 1 changed to 2x I would do this
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,1,1,0},{4,2,4,4,1,1,1,1,0,0,1}))
and the same for any others?

Also, this is a pretty simple one but (just in case you know an (incredible) way of doing it.

We have 4 stores that I will reproduce this sheet for
I then need to create another 3 sheets which ranks upstairs / downstairs and bedroom staff according to their performance.
Would the best way be to simply reference the name, department and point cells and then sort accordingly?


Really cant thank you enough for this, I would certainly have been scratching my head and scouring the net for ages

 

[cmowla]

Am I right in this


if we change the multiplier of different columns, I simply change the number here
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,1,1,0},{4,4,4,4,1,1,1,1,0,0,1}))

So if Bedroom decided that Column 10 was multiplied by 4 I would simply change
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,4,1,0},{4,4,4,4,1,1,1,1,0,0,1}))

Well, it should be:
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,1,4,0},{4,4,4,4,1,1,1,1,0,0,1}))
because the first 4 to the right of the { is column 1's multiplier.

Similarly this

OR downstairs/UPstairs column 1 changed to 2x I would do this
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,1,1,0},{4,2,4,4,1,1,1,1,0,0,1}))

would be:
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,1,1,0},{2,4,4,4,1,1,1,1,0,0,1}))

and the same for any others?

Absolutely. And if you have, for example, three or more different parts of the house to consider, you can literally "spell it all out". You can list them all. Even if their {} lists are the same for, say, upstairs and downstairs in this case, you can just copy and paste the same list but show it after each part of the house for clarity.

But the example below shows when you have different multipliers for each one:
=SUMPRODUCT(CFV(F6:P6),SWITCH(D6,"Bedroom",{4,4,4,0,0,0,0,0,1,1,1},"Kitchen",{4,2,4,4,1,1,1,1,0,4,1},"Upstairs",{1,4,4,1,1,1,1,1,0,0,1},"Downstairs",{4,4,4,4,1,1,1,1,0,0,1}))

(The above reads: "If it's Bedroom, then use this multiplier set, {4,4,4,0,0,0,0,0,1,1,1}. If it's Kitchen, then use this multipler set, {4,2,4,4,1,1,1,1,0,4,1}, etc. )

Really cant thank you enough for this, I would certainly have been scratching my head and scouring the net for ages

You're very welcome! I was going to make another video to explain things better, but I have been told to stop doing that.

And I didn't mention it, but what's great about the code for generating the conditional formatting is not just that it's easy to adjust the row numbers if you get more employees in the future. If you need to add more columns (say Q), you literally just have to add another "code block" after "P".

columnLetter = "Q"
Call LessThan(75, columnLetter, firstRow, lastRow, red, fontColorForRedCells)
Call Between(75, 81, columnLetter, firstRow, lastRow, amber)
Call Between(82, 88, columnLetter, firstRow, lastRow, green)
Call GreaterThan(88, columnLetter, firstRow, lastRow, gold)

Also, if you want to change the colors of the conditional formatting background colors to match that of the font color in your table from this post, you can.

Just change the RGBs in both the conditional formatting sub

Sub Set_Conditional_Formatting()
Dim firstRow&, lastRow&, columnLetter$, red, amber, green, gold

red = RGB(255, 0, 0)  'Red
amber = RGB(255, 230, 153) 'Amber
green = RGB(0, 176, 80) 'Green
gold = RGB(255, 255, 0) 'Gold

and the CFV user defined function: (and change the currentVal= to different values if the scores for the colors change)

Function CFV(rng As Range)
Application.Volatile

ReDim cf_Cells(1 To rng.Columns.Count)
Dim currentVal As Integer
Dim counter As Integer
counter = 1
Dim R As Range
For Each R In rng
    Select Case DFColor(R)

        Case "RGB(255,0,0)" '3 'Red
            currentVal = 0

        Case "RGB(255,230,153)" '44 Amber
            currentVal = 1

        Case "RGB(0,176,80)" '4 Green
            currentVal = 3

        Case "RGB(255,255,0)" '6 Yellow
            currentVal = 5

And if you ever need to have five degrees of measure, not just 4, clearly you would modify the above "code block" to (just add in another Between and fill in the numbers accordingly):

columnLetter = "Q"
Call LessThan(75, columnLetter, firstRow, lastRow, red, fontColorForRedCells)
Call Between(75, 81, columnLetter, firstRow, lastRow, amber)
Call Between(82, 85, columnLetter, firstRow, lastRow, blue)
Call Between(86, 88, columnLetter, firstRow, lastRow, green)
Call GreaterThan(88, columnLetter, firstRow, lastRow, gold)

for example, and therefore change/add in the RGB portions to the following (again, for example): (The new lines of code you add in in this case are marked with '************************.)

Sub Set_Conditional_Formatting()
Dim firstRow&, lastRow&, columnLetter$, red, amber, green, gold,
Dim blue'************************

red = RGB(255, 0, 0)  'Red
amber = RGB(255, 230, 153) 'Amber
green = RGB(0, 176, 80) 'Green
gold = RGB(255, 255, 0) 'Gold
blue = RGB(0,0,255) 'Blue'************************
fontColorForRedCells = RGB(255, 255, 255) 'White

Function CFV(rng As Range)
Application.Volatile

ReDim cf_Cells(1 To rng.Columns.Count)
Dim currentVal As Integer
Dim counter As Integer
counter = 1
Dim R As Range
For Each R In rng
    Select Case DFColor(R)

        Case "RGB(255,0,0)" 'Red
            currentVal = 0

        Case "RGB(255,230,153)" 'Amber
            currentVal = 1

        Case "RGB(0,176,80)" 'Green
            currentVal = 3

        Case "RGB(255,255,0)" 'Yellow
            currentVal = 5

        Case "RGB(0,0,255)" 'Blue '************************
            currentVal = 7 '************************

 

[Woodpusher147]

Well, it should be:
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,1,4,0},{4,4,4,4,1,1,1,1,0,0,1}))
because the first 4 to the right of the { is column 1's multiplier.

Similarly this

would be:
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,1,1,0},{2,4,4,4,1,1,1,1,0,0,1}))


Absolutely. And if you have, for example, three or more different parts of the house to consider, you can literally "spell it all out". You can list them all. Even if their {} lists are the same for, say, upstairs and downstairs in this case, you can just copy and paste the same list but show it after each part of the house for clarity.

But the example below shows when you have different multipliers for each one:
=SUMPRODUCT(CFV(F6:P6),SWITCH(D6,"Bedroom",{4,4,4,0,0,0,0,0,1,1,1},"Kitchen",{4,2,4,4,1,1,1,1,0,4,1},"Upstairs",{1,4,4,1,1,1,1,1,0,0,1},"Downstairs",{4,4,4,4,1,1,1,1,0,0,1}))

(The above reads: "If it's Bedroom, then use this multiplier set, {4,4,4,0,0,0,0,0,1,1,1}. If it's Kitchen, then use this multipler set, {4,2,4,4,1,1,1,1,0,4,1}, etc. )



You're very welcome! I was going to make another video to explain things better, but I have been told to stop doing that.

And I didn't mention it, but what's great about the code for generating the conditional formatting is not just that it's easy to adjust the row numbers if you get more employees in the future. If you need to add more columns (say Q), you literally just have to add another "code block" after "P".

columnLetter = "Q"
Call LessThan(75, columnLetter, firstRow, lastRow, red, fontColorForRedCells)
Call Between(75, 81, columnLetter, firstRow, lastRow, amber)
Call Between(82, 88, columnLetter, firstRow, lastRow, green)
Call GreaterThan(88, columnLetter, firstRow, lastRow, gold)

Also, if you want to change the colors of the conditional formatting background colors to match that of the font color in your table from this post, you can.

Just change the RGBs in both the conditional formatting sub

Sub Set_Conditional_Formatting()
Dim firstRow&, lastRow&, columnLetter$, red, amber, green, gold

red = RGB(255, 0, 0)  'Red
amber = RGB(255, 230, 153) 'Amber
green = RGB(0, 176, 80) 'Green
gold = RGB(255, 255, 0) 'Gold

and the CFV user defined function: (and change the currentVal= to different values if the scores for the colors change)

Function CFV(rng As Range)
Application.Volatile

ReDim cf_Cells(1 To rng.Columns.Count)
Dim currentVal As Integer
Dim counter As Integer
counter = 1
Dim R As Range
For Each R In rng
    Select Case DFColor(R)

        Case "RGB(255,0,0)" '3 'Red
            currentVal = 0

        Case "RGB(255,230,153)" '44 Amber
            currentVal = 1

        Case "RGB(0,176,80)" '4 Green
            currentVal = 3

        Case "RGB(255,255,0)" '6 Yellow
            currentVal = 5

And if you ever need to have five degrees of measure, not just 4, clearly you would modify the above "code block" to (just add in another Between and fill in the numbers accordingly):

columnLetter = "Q"
Call LessThan(75, columnLetter, firstRow, lastRow, red, fontColorForRedCells)
Call Between(75, 81, columnLetter, firstRow, lastRow, amber)
Call Between(82, 85, columnLetter, firstRow, lastRow, blue)
Call Between(86, 88, columnLetter, firstRow, lastRow, green)
Call GreaterThan(88, columnLetter, firstRow, lastRow, gold)

for example, and therefore change the RGB portions to the following (again, for example): (The new lines of code you add in in this case are marked with '************************.)

Sub Set_Conditional_Formatting()
Dim firstRow&, lastRow&, columnLetter$, red, amber, green, gold,
Dim blue'************************

red = RGB(255, 0, 0)  'Red
amber = RGB(255, 230, 153) 'Amber
green = RGB(0, 176, 80) 'Green
gold = RGB(255, 255, 0) 'Gold
blue = RGB(0,0,255) 'Blue'************************
fontColorForRedCells = RGB(255, 255, 255) 'White

Function CFV(rng As Range)
Application.Volatile

ReDim cf_Cells(1 To rng.Columns.Count)
Dim currentVal As Integer
Dim counter As Integer
counter = 1
Dim R As Range
For Each R In rng
    Select Case DFColor(R)

        Case "RGB(255,0,0)" 'Red
            currentVal = 0

        Case "RGB(255,230,153)" 'Amber
            currentVal = 1

        Case "RGB(0,176,80)" 'Green
            currentVal = 3

        Case "RGB(255,255,0)" 'Yellow
            currentVal = 5

        Case "RGB(0,0,255)" 'Blue '************************
            currentVal = 7 '************************

Fantastic.. BTW the vid helped enormously

 

[Woodpusher147]

One last thing, the sheets to rank department staff. Is that as simple as referencing the relevant cells or is there a better way?

Massive thanks again for your time

 

[cmowla]

One last thing, the sheets to rank department staff. Is that as simple as referencing the relevant cells or is there a better way?

Massive thanks again for your time

I'm not quite sure what part of what I have done that you are asking. When you use the term "sheet", I just think of the spreadsheet that we have been working on.

All I can think of as possibly what you're asking about is the parts of the formula that I put in bold below:
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,1,4,0},{4,4,4,4,1,1,1,1,0,0,1}))

Is that what you're asking about? Or something else? (And if you're asking about that is it the F11:P11 or the D11? And what is the ideal system? What specifically do you want to happen?)

 

[Woodpusher147]

I'm not quite sure what part of what I have done that you are asking. When you use the term "sheet", I just think of the spreadsheet that we have been working on.

All I can think of as possibly what you're asking about is the parts of the formula that I put in bold below:
=SUMPRODUCT(CFV(F11:P11),IF(D11="Bedroom",{4,4,4,0,0,0,0,0,1,4,0},{4,4,4,4,1,1,1,1,0,0,1}))

Is that what you're asking about? Or something else? (And if you're asking about that is it the F11:P11 or the D11? And what is the ideal system? What specifically do you want to happen?)

Sorry i wasnt more clear.
I was referencing a question i had asked in one of the earlier posts.
After the current sheet is done, i need to repeat it for 4 stores, i will use your solution , its perfect .
I then need to create another spreadsheet to rank staff in each department. Upstairs-downstairs-bedroom.
Is this simply a case of referencing the relevent cells i. E. Score , name, department. Calling all rows from 4 sheets that have "bedroom" and then sorting (ssme for other 2} or is there a better way to achieve this?


Thanks again

 

[Woodpusher147]

Sorry i wasnt more clear.
I was referencing a question i had asked in one of the earlier posts.
After the current sheet is done, i need to repeat it for 4 stores, i will use your solution , its perfect .
I then need to create another spreadsheet to rank staff in each department. Upstairs-downstairs-bedroom.
Is this simply a case of referencing the relevent cells i. E. Score , name, department. Calling all rows from 4 sheets that have "bedroom" and then sorting (ssme for other 2} or is there a better way to achieve this?


Thanks again

Hmm, looking at this for a while and appears isn't so simple (for me anyway)
Help would be appreciated>Google isn't much help hah
VLOOKUP seems to be the closest to what I need but I cant work it out