How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask ATLPROG Your Own Question

ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

Is it possible to implement graded color scale conditional

Customer Question

is it possible to implement graded color scale conditional formatting based on alphabetical order of text strings?
Submitted: 3 months ago.
Category: Programming
Expert:  The-PC-Guy replied 3 months ago.

can you please explain further what you mean?

Customer: replied 3 months ago.
of course. i have attached the document I am working on, and the sheets i am working on are Lookup List, Job Info, Roll Up 2, Roll Up, Downey and Indy.
Customer: replied 3 months ago.
On sheet Roll Up 2, I need conditional formatting exactly like in Roll Up, i.e. color graded scale based on value. my problem is that on Roll Up 2, the values in the field are names (Pfizer, Amgen, Hospira, etc) and not numbers, so excel won't naturally do conditional formatting.
Customer: replied 3 months ago.
i think i am working on excel 2016
Expert:  The-PC-Guy replied 3 months ago.

unfortunately there is not a way using the builtin conditional functions to do coloring based on names, We may be able to create a macro that simulates it..

for example if it starts with A it has a red value or RGB (FF0000) and if it starts with Z it has green for RGB (00FF00), then just manipulate that value based on what letter it starts with.

Not an easy task, and it may only be able to do 26 different colors one for each starting letter.

Customer: replied 3 months ago.
if I securely connect, and then want to upload this document to excel online later, will it have the same capabilities, or will i have to come back to get it implanted on excel online as well?
Expert:  The-PC-Guy replied 3 months ago.

as far as I understand excel on line it does not support macros. so no it would not function.

Customer: replied 3 months ago.
do you have any experience with sharepoint? my organization uses that as well sometimes. would it work if constantly uploaded and downloaded via sharepoint?
Expert:  The-PC-Guy replied 3 months ago.

no experience with sharepoint sorry

but my limited understanding of it is that it just creates a file list correct? So if you upload the .xlsm file to a folder in it, anyone accessing it should be able to run a macro from their local machine just as if you emailed the file to them. But any kind of live editing of the document where more than one user tries to simultaneously see changes, or web interface, my understanding is that there is no support for macros for that.

Customer: replied 3 months ago.
i ended up figuring it out on my own. but thanks for your help!
Expert:  The-PC-Guy replied 3 months ago.

kinda curious how you did it without a macro

Customer: replied 3 months ago.
Totally. The most important thing in this project regarding coloring (I hope my boss agrees), is that the location jobs (designated by the first two numbers in the text string) are similar color to the location names. so I basically made brackets in conditional formatting: value(left(c2,8))<15000000, for example. then added another conditional format: and(value(left(c2,8)>15000000,value(left(c2,8)<20000000 and so on so that I can get different colors to highlight those numbers. most ideally, I could do color grade scale conditional formatting based on the 8 numbers that begin the text string, but I couldn't figure out how to do it. this will have to work for the first draft of my project.
Expert:  The-PC-Guy replied 3 months ago.

well, you are talking about it working on the numeric values not the text.

So It seams to me that the big deal was being able to have this thing online or in sharepoint or something.

Well let me know if you still need by help in making it work locally knowing it wouldn't function the same way online.

Customer: replied 3 months ago.
thanks man I appreciate the help! I put it on excel online, and it appears to be working at the moment, but I will keep you posted. thanks for your help!