Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Change Color Based On Other Cell Value

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Ok, the following are the given data...
Code:

     A           B           C           D           E
1   
2    ABC         50          40          70          60
3    XYZ         20          0           15          10



and ...here's where I'm stuck right now. I don't know how to put it, but this is the best I can explain:

Level 1: If C = 0, consider B; else consider C (I'm naming this LVL1)
Level 2: If LVL1 >= D, turn A into RED; elseif LVL1 <= E, turn A into GREEN; else turn A into BLACK

Is there anyone kind enough willing to help me with this?

Thank you.


Btw, a sample excel file would be really helpful.


Similar Excel Video Tutorials

Helpful Excel Macros

Create a Bar Chart With a Macro in Excel
- Create a bar chart in Excel with this macro. You will be able to quickly and easily turn any range of numbers and data
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Email Current Workbook & or Other Attachments
- This macro will send the current workbook in an email through Microsoft Outlook. The Macro allows you to send the most
Display The Actual Link / Email Address From Links in Excel - UDF
- Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac

Similar Topics







Hi

I have put an example of my data at the bottem of this sheet, is is a small section of a larger sheet

What i would like to do is create a function that i can type into the cells to the right of the red Numbers

the function must add words and conditionally formatt the cell depending on the red value and the range that it falls.

the ranges are in the coloured box's to the right these will be used in the function

so if the red mumber is
Greater than cell H3 then i would like level 1 to appear in the cell and it to turn blue

if the red number is lower than H3 but greater than H4 i would like level 2 to appear and the cell to turn green

if the red number is less than H4 and greater than H5 i would like Level 3 to appear and the cell to turn orange

if the red number is less than H5 but greater than H6 i would like level 5 to appear and it to turn red

if the red number is less than H6 I would like Level 5 to appear and the cell to turn pink


A B C D E F G H I 1 2 3 Calculation 1 110 Level 1 217.5 4 Level 2 207.5 5 Calculation 2 110 Level 3 197.5 6 Level 4 175 7 Calculation 3 110 8 9 10

I recently tried to create conditional formatting for the following

If cell = CB turn Yellow Black text bold and itallic
If cell = S/Lit turn Bright Green Black text bold and itallic
If cell = NI turn Gray 40% Black text bold and itallic
If cell = email turn Aqua Black text bold and itallic
If cell = MR turn Lime Black text bold and itallic
If cell = Cleansed turn Rose Black text bold and itallic
If cell = Dup turn Tan Black text bold and itallic
If cell = DNC turn Red Black text bold and itallic
If cell = KW turn Light Orange Black text bold and itallic
If cell = Lead turn Lavander Black text bold and itallic
If cell = LTC turn Plum with white text Bold & Itallic
If cell = Appt turn Pink Black text bold and itallic
If cell = Quote turn Light Blue white text Bold & Itallic
If cell = XAPPT turn Sea Green Black text bold and itallic
If cell = XC turn Dark Yellow Black text bold and itallic

Obviously there is only 3 conditionals available, not 15...
Any other way to do this? maybe a macro? thanks!


Hi,

I have created a project status report in Excel. Column E is my "progress" column with the rows being the different projects. With the click of the mouse I want to be able to change the cell color AND write the color in the box (want to write the color so if it is printed in black and white, someone can read the color).

All cells will start white and blank (no writting). With 1 click of the mouse it should turn green and "green" should appear in the cell, with a 2nd click of the mouse it should turn yellow and "yellow" should appear in the cell, a 3rd click should turn it orange, a 4th click turn it red, and a 5th click turn it back to white with no writting.

All of the code I have found online changes it from 1 color back to white, but I can't figure out how to allow it to do multiple colors.

Any help would be appreciated!


Evening

I have been racking my brains out for ages with this.

I will first explain what I am trying to achieve. I need a cell to turn either red, if we have failed a "fix time" or green if we have kept within a "fix time"

We have two levels:

1
2

We have three contracts:

a
b
c

The fix time hours on level 1 and 2 for contracts "a" and "b" are 2 and 4 hours respectively.

The fix time hours on level 1 and 2 for contract "c" are 3 and 6 hours respectively.

Please see the attached workbook. The duration of an issue is automatically calculated in [h]:mm in cell E2 based on the start date and time as well as the end date and time in cells c2 and d2 (dd/mm/yyyy hh:mm )resppectively.

I need a set of 6 formulas (rules) under conditional formating to turn the cell red or green depending on the severity level "A2", the contract "B2" and lastly the automatically calculated time in "E2" to decide if we failed it(RED) or beat it(GREEN).

Any help or just one workig formula would be massively appreciated, As i can use that as a base for the other 5.


Does anybody know the code to change the color of cells. I have 6 differnet cells that need to change colors based on a lookup. Meaning if cell b22 has data in it it will change red. Cell E22 will turn Pink if there's data in it, cell b23 will turn light blue with data, cell d23 will turn green with data, and cell b24 will turn orange with data.


All,
I am using Excel 2007. I have a spreadsheet to keep track of scores. I would like to set conditional formating based on length of time. Ex.
All Students have to maintain a 3.0. Any student with 3.0 and above; cells should be green. If a student falls below 3.0; I would like the cell with the score highlighted in yellow for caution. I would like the supervisor to begin coaching action level one. If the score has not improved by day 15; I would like the cell to turn orange and the next coaching evel 2 begin. If the students score still has not improved within the next 15 days; I would like the cell to turn red for coaching level 3. However; if the student improved within 15 days and score is 3.0 or better; cells turn green and the process begins all over again. I hope this make since. I need to let the coaches know when and who to coach at what level.

Thanks!


All,
I am using Excel 2007. I have a spreadsheet to keep track of scores. I would like to set conditional formating based on length of time. Ex.
All Students have to maintain a 3.0. Any student with 3.0 and above; cells should be green. If a student falls below 3.0; I would like the cell with the score highlighted in yellow for caution. I would like the supervisor to begin coaching action level one. If the score has not improved by day 15; I would like the cell to turn orange and the next coaching evel 2 begin. If the students score still has not improved within the next 15 days; I would like the cell to turn red for coaching level 3. However; if the student improved within 15 days and score is 3.0 or better; cells turn green and the process begins all over again. I hope this make since. I need to let the coaches know when and who to coach at what level.

Thanks!


here is a formula that is in use..

IF( INDIRECT("'Roster1'!A1'")=""," ",INDIRECT("'Roster1'!A1") )

so.. if A1 is null return "space" else copy A1

I want to add now..

if A1 = "STR" turn this row green
if A1 = "LTR" turn this row blue
if A1 = "SGE" turn this row red
else
leave it, or turn it black

i looked at conditional formatting.. however not only could i not get it to work, it appears to me it only looks at 1 cell and cant change the whole row color.
let me know if u have any ideas
mdpx


Is there a way I can make my check box change color when it's checked? I'd like to have it uncolored and then turn green when checked. Not the cell, but the checkbox itself. I can't seem to make the check box part of the cell, if that would be an easier route to go please let me know. If I have to turn the cell green and not the check box, I'd love for the whole row to turn green. I attached a sample of the sheet I'm trying to build. Thanks for any help you guys can give.


I have something that I track that needs to take place in a given month and I need a visual clue that the month is approaching. What I would like is the month before I need to take action, that cell turns green. If I do not take any further action I would like that cell to turn yellow when the month that the action is due comes around and then turn red once that month has passed. The target month can be ANY month of the year, so the formula needs to be able to distinguish between all of the months and react accordingly.

So if the target month was Jun-11 then on May 1st 2011 the Jun-11 cell would turn green. ON Jun 1st the cell would turn Yellow, and on Jul 1st turn red. The next line I am tracking might have Feb-12 entered. So on Jan 1st of 2012 the Feb cell would turn green and on Feb 1 turn yellow and Mar 1 turn red.

Clear as mud?


Hi all,

I'm new too this board and I've been searching all over for the answer too my question. Currently I am using the conditional format but I can not copy the color cell's too another sheet. So I need a vb code. Below I have 8 numbers in cell $A$1:$H$1 and numbers will be randomly generated. What I am trying too do is too match the number with corresponding number under the color heading. So the cell with the number 8 will turn red, number 1 will turn blue, number 3 will turn yellow, number 6 will turn green. Can some help. Thanks


From this example:

8 5 6 3 7 1 4 2 8 1 3 6

Too this:

8 5 6 3 7 1 4 2 8 1 3 6


How would I go about doing the following?

I have 2 columns a & b each filled with data.
I want each cell in either column to turn green if its exact value is in the other column anywhere, and if its not to turn red. I believe I want to use conditional formatting for this but I can't quite get my head around the formula I need to use.

example

Cell A1 contains abc123
if exactly abc123 is in any cell in column B i want A1 to turn green, if it's not I want it to turn red.

The same thing in for the cells in column B comparing to column A.

Thank you in advance for any help.


Hello,


Not sure if this paticular has been posted before.. I did a search and checked different thread to no avail..
Here is what I am looking to do:

I have data in column B2:B88 which are lengths. In column C2:C88 are the cumulative sums of column B. I want the cell colour in column C to change to green based on the cumulative sum of the data in colum B. Basacilly after every 1000m cumulative count the next higest number cell will turn green.
So for example C24 will turn green as it is > 1000. I do not want C25 to C45 to turn green.
The next number to turn green will be C46. I have just coloured the cell manually.
Note: Values in column B will change.


I have attached an example. Any help would be greatly appericated.

Thanks.

Hello,
Excel version is 2003, OS is Win XP Sp2. I have 3 different date fields and I want 3 other fields to change colour based on these date fields to tell me Red, Amber or Green so that I can take appropriate action - send out reminders/make enquiries etc.

I am a new excel user and feel comfortable with using the product, but have not done aynthing with VBA yet.

The 3 things I want to do a
If Date 1 is more than 12 months in the future of todays date, turn Field 1 Green, if 6 months, turn it Amber, if 3 months turn it Red. If there is less than 90 days from Date 2 turn Field 2 Green, if 60 days Amber, if 30 days Red. If Date 3 is less than 12 months ago turn Field 3 Green, if more than 12 months, turn it Red (no Amber required). Apologies for the slightly inelegant explanation. Any assistance you can give me would be very welcome

rgds

Graham


No Level Charge (Includes suture removal, packing removal & test results) (10) Left Without Being Seen (11) Level I (0 - 10)
(Includes Scheduled Rechecks) (3, 26, 32) Level I w/Additional Service Level II (20 - 30) (4, 27, 33) Level II w/Additional Service Level III (40 - 60) (5, 28, 34) Level III w/Additional Service Level IV (70 - 90) (6, 29, 35) Level IV w/Additional Service Level V (100+) (7, 30, 36)
I'm trying to find a formula based on the above Levels and it meeting the point system criteria. Example: If H10 is between 0 and 10 for Level one, it will put an "X" in that box. I know this is going to turn out to be something simple but novice here. Thanks in advance


Hi to all

I've been searching for colorfuntion or colorfont in this site and Google for my likeness but have not luck.

I have to create a spreadsheet for others who dont know much about Excel.

Numbers in E column is to change color if B column in same row turn yellow.

There are number 1 to 5 in E column

Like number 5 in Column E turn blue if cell in B column in same row turn yellow
and number 4 in Column E turn green and number 3 in Column E turn pink and so on.

I tried this code but have no luck

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long
Sheets("Sheet1").Activate
For i = 5 To 26
    If Range("E" & i).Value = "5" & Range("B" & i).Value = Interior.ColorIndex = 6 Then
       Range("E" & i).Interior.ColorIndex = 5
   End If
    If Range("E" & i).Value = "4" & Range("B" & i).Value = Interior.ColorIndex = 6 Then
       Range("E" & i).Interior.ColorIndex = 10
   End If
       If Range("E" & i).Value = "3" & Range("B" & i).Value = Interior.ColorIndex = 6 Then
       Range("E" & i).Interior.ColorIndex = 7
   End If

Next i
End Sub



I attached sample book for you to see

NumberChangeColor2.xlsm


I have a simple sheet where I track due dates. These dates come due based upon the calendar month, not number of days. I am trying to have cells change color based upon month before (green), month of (yellow), and month after (red). I think that I could probably do that based upon days, but I need just the month. For example if the target date in the cell is JUL-11, on JUN-11 I would like that cell to turn green. On 1 Jul (JUL-11) I would like the cell to turn from green to yellow and come 1
August (AUG-11) the cell would turn from yellow to red. Doing this by >30 etc, really does not accomplish what I need, as the requirements are strictly based upon calendar month, not days.

I am somewhat familiar with using conditional formatting and using new rule, but haven't a clue as to how to write the formulas.

Thanks

Hi, im trying to combine Lookup with Index and match.

My problem is that i would like to Lookup up a certain value in a range to return a value representing the range but with 3 criteria to Index or Match - Not sure if its making any sense.

Here's how it looks like.

LVL Name Tenure Sales % Sales % Score Quality Quality Score LVL0 John Doe1 1-30 Days 73.54% 85.00% LVL1 John Doe2 BSC 33.00% 90.00% LVL2 John Doe3 BSC 65.00% 69.00%

Level Tenure Metric Score Ranges Remarks LVL0 1-30 Days Sales % 1.00 0 0.6899 Failed LVL0 1-30 Days Sales % 1.50 0.69 0.7099 Failed LVL0 1-30 Days Sales % 2.00 0.71 0.7299 Passed LVL0 1-30 Days Sales % 2.50 0.73 0.7499 Passed LVL0 1-30 Days Sales % 3.00 0.75 1 Passed LVL0 31-60 Days Sales % 1.00 0 0.6899 Failed LVL0 31-60 Days Sales % 1.50 0.69 0.7099 Failed LVL0 31-60 Days Sales % 2.00 0.71 0.7299 Passed LVL0 31-60 Days Sales % 2.50 0.73 0.7499 Passed LVL0 31-60 Days Sales % 3.00 0.75 1 Passed LVL0 61-Reg Sales % 1.00 0 0.6999 Failed LVL0 61-Reg Sales % 1.50 0.7 0.7199 Failed LVL0 61-Reg Sales % 2.00 0.72 0.7399 Passed LVL0 61-Reg Sales % 2.50 0.74 0.7599 Passed LVL0 61-Reg Sales % 3.00 0.76 1 Passed LVL1 BSC Sales % 1.00 0 0.7199 Failed

Condition:
- On the Blue Cell, it needs to Lookup the value (73.54%) in the ranges shown above, considering LVL, Tenure as another Criteria.

Really appreciate your help on this.

Thanks.


Well, I need a little help here and tought you guys could help. I am using Excel 2007. I have my B row full of dates for item I need to turn in. I would like the cell to turn green when that date is in the current month and Red when it goes past the date in the cell.

Example:
B2 22-Apr-09

That cell should be Green because we are in April right now. It should turn red once it goes past the 22nd telling me it is late.

Thank you for your help and time.


Hi

I have a 3 level dependency dropdown structure that I need you help with please.

I am able to achieve my goal up to the second level but can't seem to get the third level to work.

Looking at the attached sample file you will see that columns A,B,C represents a typical 3 level structure for my categories.

Columns L,M,N is where I've applied the Data Validations. The user should only be able to select an option in the dropdown based on the previous level's selection.

Most of the formulas that I've used was based on the article which I found he http://www.contextures.com/xlDataVal13.html

I'm sure you will be able to see what it is I'm trying to achieve based on the sample file but please tell me if I did not explain myself well enough.

Thanks for you help.

Is there a way to change the color of an entire row, based on the response of a certain cell?

if G3 = Processing then that whole line would turn green, or
if G4 = Closed then that line would turn Red,

and so on.

If so, what would be the easiest way to do it


Here is what I can do.

A10 NameA if something in c32:W32 other than 'xx' then turn red
A11 NameB if something in c33:W32 other than 'xx' then turn red


A32 NameA if something in c10:W32 other than 'xx then turn red
A33 NameB if something in c11:W32 other than 'xx' then turn red

It gets more complicated if the name is in 3 or more places. (I cant figure this formula out) I want it to turn red if there is a 1 or greater, even if there is an X in one of the other two.

Example:

NameA shows up in 3 places.

A10 NameAif something in c32:W32 or c15:W15 turn red if there is an 'xx' in one but a 1 or greater in the other still turn red. If there is only 'xx' or nothing do not turn red.
A11 NameB if something in c33:W32 other than 'xx' then turn red


A15 NameA if something in c32:W32 or c10:W10 turn red if there is an 'xx' in one but a 1 or greater in the other still turn red. If there is only 'xx' or nothing do not turn red.


A32 NameAif something in c10:W10 or c15:W15 turn red if there is an 'xx' in one but a 1 or greater in the other still turn red. If there is only 'xx' or nothing do not turn red.
A33 NameB if something in c11:W32 other than 'xx' then turn red


See http://www.geocities.com/neoeny152/schedule.xls
for an example of what I am trying to do. (cant figure out the 3 name part just yet so this only includes the 2 name)

Here is what I want to do.

A?1 NameA - if NameA is found in any another A?2 A?3 A?4 field then turn C?1:W?1 red if C?2:W?2 C?3:W?3 C?4:W?4 is greater than 0 if there is an 'xx' in one but a 1 or greater in the other still turn red. If there is only 'xx' or nothing do not turn red.


I want excel to automatically know if a duplicate name shows up, and this could happen in more than just two places. The name could show up in 3 or 4 places and I need cells c-w to turn red when a 1 is in any of the corresponding c-w cells. Except for the one that has something in it, that one needs to stay white.

Is this even possible? (my head hurts)


I have two spreadsheets and I would like to Vlookup exact info and based on the return, turn another cell on the return sheet a certain color.

So say I entered this formula in column B sheet2

Code:

=Vlookup(A2,Sheet1!$A$2:$C$1000,3,0)


and there was only three possible returns: "Drop" "Add" and "Same"

If the return is "Drop" turn the corrosponding cell in column D the color Red
If the return is "Add" turn the Corrosponding cell in Column D the color Green
If the return is "Same" do nothing

...Is any version of this possible? Even a starter kit here that does a simple version or shorter version of this would be great, or just anything that accomplishes something similar to this. Thanks


I am struggling trying to find the proper way to set this up, yet it seems like it should be so easy.

Let's say I have a set of data. The data can be just about any number, but I want to put the low range in red, the middle range in yellow and the top range in green.

For the top 35%, they would be green
For the middle 30%, they would be yellow
For the bottom 35%, they would be red.

For example:
if my Data set is: 1, 25, 45, 51, 80, 100
1 (would turn this cell red)
25(would turn this cell red)

45(would turn this cell yellow)
51(would turn this cell yellow)

80(would turn this cell green)
100(would turn this cell green)

I see where I can put above average, below average, or to set a specific range.

But is there a way to select the above without having to set 3 rules? i tried looking at stuff here and on Google, but could not find what I was looking for.

How do I set it so that a range of numbers can be put into the colors I am looking for?

I hope I explained that well enough.

Thanks


Hello ... in microsoft excel 2007, I'm looking for a way to highlight cell D,2 based on the value of cell C,2. Here is the criteria basis:

If C,2 = HOLD then D,2 turn Yellow or
If C,2 = TAKEN then D,2 turn Green or
If C,2 = AVAILABLE then D,2 turn Grey

Thanks in advance for your assistance.

HDfatboy03