Change cell value based on the color of another cell

Woodpusher147

Board Regular
Joined
Oct 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Hello
I hope someone can help with a (in my not super excel mind) very complicated issue.
1633520453103.png


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
1633519851369.png
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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).
 
Upvote 0
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 :)
 
Upvote 0
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".

VBA Code:
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
VBA Code:
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)
VBA Code:
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):
VBA Code:
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 '************************.)
VBA Code:
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

VBA Code:
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 '************************
 
Last edited:
Upvote 0
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".

VBA Code:
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
VBA Code:
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)
VBA Code:
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):
VBA Code:
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 '************************.)
VBA Code:
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

VBA Code:
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
 
Upvote 0
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

:)
 
Upvote 0
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?)
 
Upvote 0
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
 
Upvote 0
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 :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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
Back
Top