Column A has Description
Column B has Numeric Values
I select Numeric Values, Conditional Formatting>Color Scales> (Then select first scale).
This applies the heat mapping color to my numeric values.
I would like the cells in column A to match that of its adjacent cell in column B.
What's the best/easiest way to do this?
Thanks!
have you tried the format painter? (little paint brush under the home tab)
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Yes, that copies the conditional formatting, which would colorize the cells based on their numerical cell value order. Since it is text, it does not do anything for coloring.
I've seen copying cells to Word Document, then copying from word document back to excel. This removes conditional format keeping the cell color. Looking for a more elegant solution.
Perhaps modifying the conditional formatting to include adjacent cell? or maybe VBA to copy conditional formatting color and paste as regular cell color?
Last edited by ImpetuousRacer; 05-01-2019 at 02:14 PM.
thought I'd give it a shot, I don't use the heat mapping feature so I wasn't sure.
In short you can't directly do it through method described.
There are number of workarounds.
1. Create 5 separate CF using formula. You can use hard coded value, or use separate Percentile lookup table for this.
2. Use vba to replicate cells.DisplayFormat.Interior.ColorIndex from column b to column a's cells.Interior.ColorIndex.
See attached for method 1.
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
I like the sounds of Method 2. Do you have a VBA code I could use? Thanks for all the help!
What version of Excel do you use? I'd recommend updating your profile with your version.
.DisplayFormat property is only available for Excel 2010 and later only.
Something like below as an example: I used Interior.Color, instead of .ColorIndex, as I noticed that some DiplayFormat's ColorIndex not being same on Cell's ColorIndex.
Sub Demo() Dim cel As Range For Each cel In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Cells cel.Offset(, -1).Interior.Color = cel.DisplayFormat.Interior.Color Next End Sub
Thanks, that worked.
I'm working with a multitude of cells through workbooks and sheets.
The offset would always -1, but is there a way for the selection to be dynamic based on selected cells?
If not, this works great!
I have updated my profile. Using 2013 (work) & 2019 (home).
Thanks!
Last edited by ImpetuousRacer; 05-01-2019 at 05:44 PM.
Quite tricky method, but I found it was working in my office365:
->Copy the heat-map generated table from excel to power-point,
->it will convert the number and color as it is, not conditional formating (heatmap) anymore.
->Then you go back from powerpoint to excel, copy and paste in excel.
the cells are now colored as the heatmap-color, but not as conditional formatting.
->Then you can put text instead of numbers in there without affecting the color anymore.
I hope it works for you!
Have a good day today!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks