Formatting a cell based on colour of another cell

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
239
Office Version
  1. 365
Platform
  1. Windows
Hi there,

I have a sheet and a column that I manually apply a shade to cells based purely on reading some text in another cell (on the same row). This is called the Grading column. I use four colours in this column; Red, Amber, Green & Yellow. I've then created a separate column that I want to look at the shaded cells and apply an 'R' in the case of Red cell, 'A' in the case of Amber cell...and so on. I've done this so it will help me with producing Pivot tables.

I've attached a paired down version to make it easier to review.

Any assistance would be gratefully received.

Regards

Ben

AMP Grading_Mar-21.xlsx
ABCD
1
2GradingResult
3O
4R
5Y
6G
7
Sheet1
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formatting a cell based on colour of cells [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
239
Office Version
  1. 365
Platform
  1. Windows
Apologies for this, not something I was aware of. I posted it the second time on Mr excel because I had updated XL2BB in my add-in and thought that would better present itself following comments on the Excel forum. I've posted the link below.

Please can someone take a look and provide some help with the query.

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
As has been said on EF you really need VBA for this, but you seem to have ruled that out.
 

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
239
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm not overly proficient with VBA to be honest and don't use it enough to understand basic principles of it. I'll look at the VBA solution again, though I will have to change the ranges, etc for the real sheet and that's what I'm referring to when I talk about the principles.

Regards

Ben
 

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
239
Office Version
  1. 365
Platform
  1. Windows
…...unless in a Pivot table you can filter coloured cells? This is where I started from as I was wanting to create Pivot table and be able to analyse the data by colour and it looked like that wasn't possible and so I was looking for word around, hence the original post to create like a helper column for analysis in a Pivot table.

Regards

Ben
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In Excel colour should be used to highlight/emphasise certain information, it should not be used as data which is what you seem to be doing.
 

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
239
Office Version
  1. 365
Platform
  1. Windows
I suppose one way is to conditional format the column, so that if a type the letter 'R' in the cell (denoting Red), it will turn red through the conditional format formula. Same goes with the other colours using the first letter of the colour; A(amber), G(green) & Y(yellow).

I assume the Pivot table will pick up the letter in the cell for analysis purposes?

Ben
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
I would think so, but as I have never used pivots & have no idea what your data is like, or what you are trying to do. I cannot say.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

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