Any other Excel feature to replace clunky CF design?

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Hi,

Excuse the vague title, I have the following in a spreadsheet:

Temp.PNG


P4:Y4 are filled with formula that = "l" (lower case letter L) if Z4 is in the interval 0-10 inclusive, otherwise empty string
P4:Y4 then contain CF which are triggered if the cell value is "l" - RGB fill colours listed below, font and cell background have same RGB codes


I do not need this colouring to fill 10 cells, but it does need to reflect the score in Z4 and implied behaviour.

Similar to a Insert->Sparkline or Column, is there any Excel feature that can do this kind of gradient fill in a single object?

A formatted chart object won't suffice as I would like this to be on each row of data.

Any ideas or suggestions?

TIA,
Jack
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Conditional Formatting does have data bar but it only fills with a single solid colour or graduation of that same colour.

I'm looking for a data bar where the colours are mapped and allows for multi colours within a single data bar.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825
If you change the formula as shown, you can use a color scale:

Book1
PQRSTUVWXYZ
41234567891010
Sheet4
Cell Formulas
RangeFormula
P4:Y4P4=IF(AND($Z4>0,$Z4<=10),COLUMNS($P:P),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
P4:Y4Other TypeColor scaleNO
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Hey @Eric W thank you for your reply. The cell formula works great and I like how you've anchored it at one end :)

I'm unclear how to apply the cells with CF as you've suggested, I'm stuck on finding "Other Type"
I can see how this would be easier than specifying RGB for each needed cell. What am I looking for or where to apply Other Type from the CF menu/dropdown?


PS Am I right to conclude it's not possible to do this in a single cell or a single object (not a chart), then replicate for other rows?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825
To apply the CF to a range, select the range, say P7:Y10, click Conditional Formatting > Color Scales > and pick the top left one. Insert the formula as shown. You should end up with this:

Book1
PQRSTUVWXYZ
7123456789101
8          0
9123456789103
10123456789104
11
12
133
145
Sheet4
Cell Formulas
RangeFormula
P7:Y10P7=IF(AND($Z7>0,$Z7<=10),COLUMNS($P:P),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q14Expression=(R14>0)*(R14<=10)textNO
P14Expression=(R14>0)*(R14<=10)textNO
P7:Y10Other TypeColor scaleNO
P13Expression=(Q13>0)*(A13<=10)textNO


The P13 cell is my attempt to put the scale in a single cell, using the Fill Effects option. It only allows 2-color ranges though, so it shows red to green. I then tried 2 formulas in 2 cells, red to yellow, then yellow to green, with the result as shown. The XL2BB doesn't seem to handle gradients, so here's what it actually looks like:

1616519608139.png
 
Solution

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
I got a similar effect to work with a 3-colour scale and found the same issue using the gradient too.
I think it's only possible to do on a per cell level and using both formula and CF application.

Bit of background, I'm creating a normalised table for a client's data and each row has a column for a derived score (Z in above) but annoyingly, the paymasters, want to see the colour grading in the same table too (i.e. 10 columns after Z, then more data) - it's just a number with a 1:1 map, why not save it for the output, but No! Must be shown in data table, so be it.

I'll mark above as solution, but thank you @Eric W for looking into it :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,545
Members
417,151
Latest member
ChickenTenderer

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