Get Conditional Formatting To Change The Cell Color Based On A Few Conditions?
Jul 19, 2013
I'm trying to get conditional formatting to change the cell color based on a few conditions.
I want the cells in column D to turn red if;
- The number in column B has a match in column A
- And the corresponding cell in column C is not blank
- And the corresponding cell in column D is blank
In the example below, cell D4 should be red
If there's a match (Column B to Column A) and;
- If cell in column C is not blank and cell in column D is not blank, do nothing.
If there's a match (Column B to Column A) and:
- If cell in column C is blank and cell in column D is blank, do nothing.
Here's the formula I attempted but didn't get it to work.
IF(AND(C2"",D2=""),MATCH(B2,$A$2:$A$15,0),IF((AND(C2"",D"")),"",""))
View 3 Replies
ADVERTISEMENT
Jun 26, 2013
Via conditional formatting, I am searching to change the color of a specific word in a cell (not the color of everything in the cell, but only that specific word - and the word repeats in the cell). A function such as =isnumber(search("NOK";A1)) colors all the cell values, which I do not want to do. How i can perform this task?
View 1 Replies
View Related
Oct 9, 2013
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies
View Related
Aug 9, 2014
I am trying to work with conditional formatting to turn a field a specific color based on two conditions, one of which is variable. The first condition is a list of ongoing events (which can change) and the second condition is a set of known milestones. I want to create a control (check boxes?) that will turn a given field (which has dates in it from another calculation) to either Red, Yellow, or Green.
I was thinking two Dropdown boxes - one for the events and one for the milestones. The user would choose an event, choose the milestone, then choose a checkbox or something to change the color of a field elsewhere in the spreadsheet. Right now, I'm doing this in a very clumsy way by having three checkboxes for each milestone for each event. There are five milestones per event (so 15 checkboxes per event on Sheet2). I will likely have 70+ events so I was hoping for a better way to do this. I've done this for the first three events and need a more efficient way of doing this.
See the attachment : Event Milestones based on Go Date Uploadable.xlsx
View 10 Replies
View Related
Oct 25, 2007
I would like to be able to put a value into a cell. By doing this I would like another cell next to it to turn a color. With that said, is it also possible to format a cell with several possible scenarios in one formula to be able to change the cell next to it a color?? Example is this lets say Cell B1 could have the letters, A B C D or E entered. So that the Cell A1 turns say the color Red.
View 2 Replies
View Related
Mar 5, 2007
Cell B157 is changed by checkbox 1= TRUE, 0 = FALSE
Cell H1 and Cell H2 are percentages changed by the user.
Range(AI8:AQ8,AI12:AQ12,AI16:AQ16,AI20:AQ20, etc) each cell in the range contains a formula, and is protected from the user.
I need the contents of each cell in the range to change font color (independent of each other) when the formula of any of the cells within the range changes - to the following
If B157 = 1
Cells < H1 (a percentage changed by user), would turn Red
Cells > H1 but < H2 (a percentage changed by user) would turn Yellow
Cells > H2 would be Green
If B157 = 0
Cells < H1 (a percentage changed by user), would turn Green
Cells > H1 but < H2 (a percentage changed by user) would turn Yellow
Cells > H2 would be Red
I need it to fire whenever the value of one of the cells in the range changes.
I have tried If statements, Select Case, and a combination of both but just can't get it!
View 9 Replies
View Related
Aug 6, 2008
My Excel skills are basic at best, with no knowledge of VBA other than finding the VB Editor. What I'm trying to do: Attached is the file I'm working with, to give you a better idea. When a name is selected in column B, the cells in the non corresponding columns E to N should have a black background color. For example, when you select Mike in column B, the cells in that row in columns I to N, named Dave and Dean, should become black. If you select the name Dave, columns E to H (Mike) and M to N (Dean) should become black. In addition, the background color of the non-black cells should change depending on the status.
OK : green
FAIL: red
N/A: orange
Exceptions: blue
The attached file shows a few possible results, manually entered.
View 7 Replies
View Related
Dec 4, 2012
I know how to set a conditional for a cell to change a color when i apply a specific value, but how about if i want to set E2 cell to change to green when i place a 'X' on F2?
Pretty much I want 2 columns that say Yes and the Other No. When i place a X on Yes that other cell turns green, if i place a X on No that other cell turns red.
View 5 Replies
View Related
Aug 3, 2009
I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:
Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.
i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow
However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).
View 4 Replies
View Related
Aug 9, 2013
Summary of performance of various products against target is as follows,
Product vs Target
Color Code
Result
CH4OH
Green
1.0
[Code] ........
I need the final result automated as follows,
If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red
Is there a way to automate this?
View 8 Replies
View Related
Jun 14, 2013
1 2 3 4 5 6
1
2 x x x x
3 x x
4 x x
5 x x
6 x x
Assume that is the table i have. In row 1 i need conditional formatting such that if any of the columns contain an x the first row should be color coded. In my above example every column in row 1 will be color coded expect for row 1 column 6 since there are no x's for any any row in column 6.
How can i do this in excel.Also instead of x's if i have manually entered color can we do the same ?
View 1 Replies
View Related
Dec 29, 2013
Here i want to color my cell based on their dates for example : 01-Jan-2014 to 10-Jan-2014.
For the respective date i want to color my bars from G10 to P10.
View 6 Replies
View Related
Apr 28, 2009
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters.
Samples of what I've tried:
=IF(LEFT(L23,1)=P)
=IF(MID(L23,1,1)=P)
etc.
I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?
View 5 Replies
View Related
May 29, 2012
I have a spreadsheet that uses VB macros to calculate sums of cells based on the font color of the numbers inside. It used to be fairly easy going through each cell and "classifying" them by color, so that my macros can go ahead and sum the numbers in each respective color's cell... but now I have a huge amount of numbers and would like to automate the process somewhat. Here's an example using the A and B columns:
flight $400
hotel $150
hotel $130
meal $20
meal $15
flight $350
I tried using conditional formatting to automatically change the color of the adjacent cells based on the presence of a keyword such as "flight" or "hotel", but this change is only cosmetic, and doesn't actually change the font color (it is still the default black, hence why my color-summing macros won't work!).
I'm including a sample macro for what I use to color-sum my cells, but what I am looking to automate the color-coding process based on looking for keywords as explained above in my example. Here is one of the working color-summing macros (for red, in this case) if you'd like to use it as a reference:
Function SumRed(SelectedCells As Range)
' Adds the values of the cells where the font colour is red(3).
Dim Cell As Object
Dim x As Double
[Code] ...........
View 9 Replies
View Related
May 25, 2007
I'm trying to make a tracking sheet of upcoming annual inspections for different pieces of equipment. I've already got a column set up listing the inspection due date. Using three conditional formats, the individual cells change color based on the amount of time until the inspection is due (green normally, yellow at 30 days out, red past due).
What I'd like to do is have the entire row of information change color based on the color of the cell the inpection due date is listed in. For ex. J6 lists the inspection date as 24-Feb-07 and is therefore red, I'd like A6-I6 and K6-P6 to change to red as well. And when the inspection is compleate and I manually change J6 to say 25-May-07, I'd like all of row 6 to change green based on the fact that J6 is already going to.
View 5 Replies
View Related
Jan 27, 2007
I'm trying to get the font to turn red in column G if it is greater than or equal to column I, and get it to turn orange if it is between 80% of I and I. This is what I've got so far.
Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("G" & Target.Row) >= Range("I" & Target.Row) Then Range("G" & Target.Row).Font.ColorIndex = 3
If Range("G" & Target.Row) >= Range(("I" & Target.Row) * 0.8) Then Range("G" & Target.Row).Font.Color = 45
End Sub
View 6 Replies
View Related
Jul 18, 2013
I am trying to set up conditional formatting in cell C4 to change the font to a smaller number based on the value of C4. If C4 = 0 then Font Calibri 16, otherwise Font Calibri 24. However, the font size is not availabe, it is greyed out. Is there another way to accomplish this without using VBA? In the worksheet could I use an if() statement to change the font size?
View 6 Replies
View Related
Feb 4, 2007
I have one cell (M6) wich will have one of the following values:
Pale Yellow
Yellow
Yellow Orange
Orange
Red Orange
Red
Red Violet
Violet
Blue Violet
Blue
This cell is filled in by a formula.
I would like this cells (M6) color, to change to the approriate color.
View 9 Replies
View Related
Dec 3, 2009
Is it possibe to change another cells color (Fill) based on what is placed in that cell? Example: A1 =if there is an "x" in A2 highlight A1 Green, but I have information in A1 also. Or if I had a different cell say= if there is an X in A1 Highlight A1 Green ect.. This key is to highlight a cell that I have information in based on a different cell having an X in it. If ther eis no X in the cell I have indicated, nothing needs to happen.
View 2 Replies
View Related
May 11, 2006
Using conditional formatting I can change the font color (to blue) of a cell if it meets a certain criteria. Now what I would like is to display a message when the color changes to blue. The cell range is E26 to E40.
View 9 Replies
View Related
Oct 11, 2013
I am trying to do some conditional formatting to highlight rows based on several conditions, I don't know if this is possible, but these are the criteria:
AAPL
tt
10/1/2013 11:14:15
GOOG
ll
10/1/2013 11:14:20
[code].....
I want to highlight a row if:
- the difference in time value is < 20 seconds
- the first symbol column is the same
- at least one, but not all of the rows meeting the above criteria contains a value in the second column from a given list (in this example, say tt is on this list)
So if done properly the logic would highlight the first and third rows, but would highlight none if none of the values in column two match my list.
I think the test criteria would go something like this, however I know that there are problems with my row references.
=and(abs($C2-$C3:$C65536)<0.0003,$A2=$A3,match($A2,Sheet1!A:A,0)>0)
View 2 Replies
View Related
May 23, 2014
Is it possible to change color for arrows icons in CF- icon sets? I would really like to have a five arrows pointing in different directions (all in different colors).
Can I do it in conditional formatting or should I go for "Wingdings" fonts and then change font color?
View 2 Replies
View Related
Jul 8, 2008
I have a simple sheet that has columns I-K. My issue is that in column K I am dividing J/I. If both J and I cells have a "0" then I get the #DIV0 error. If both J and I cells are blank then I get the #VALUE! error. I need it so that if the cells in J and I have a zero in them then it places a 0 in K. But if both J and I are blank then it puts something else that I can have Conditional formatting change the font color so column K looks blank. I think the IF formulas I am try to use recognize blank cells as zeros as well.
View 9 Replies
View Related
Feb 8, 2009
I want to create a chart than logs the progress of a project at work. Column 1 will have a series of target dates for certain tasks and column 2 will be filled in with the completed dates. I want automatically colour a cell (RC) based on the date given in the cell to the left (RC[-1]) giving a traffic light style warning of an upcoming target date and colour coding the cells when a task is completed on time (or not!). I can work out what the conditions are by creating 2 sets of 3 conditions (1 set if cell (RC) is empty and 1 set if cell (RC) has a date or "N/A" in it).
My conditions are:
1. Turn the cell background colour red (action overdue) if:
Formula Is:=IF(AND(RC[-1]"",RC="",RC[-1]
2. Turn the cell background colour orange (action required) if:
Formula Is: =IF(AND(RC[-1]"",RC="",RC[-1]
View 9 Replies
View Related
Mar 29, 2012
I have a date in column A. I want to format to YELLOW column F based on whether column F is blank and 20 days past column A. I also want the formatting to change colors to RED if it remains blank past 30 days. Then, when column F is filled in I want the formatting to be removed.
I am working in Excel 2010
View 1 Replies
View Related
Jun 25, 2009
The code below turns numbers and dates on my worksheets(1) to text priorities on my worksheets(3), I now need to color the cell backgrounds of the results on worksheets(3). With formulas I know I can conditional format up to 3 colors (or 4 utilising the background) but I am after 6. I also know that you will look at my code and laugh as I should have done the color coding with the initial commands, but I struggled with it for 2 days and simply couldn't get it to work.
In the VB below it all works off a command button and up to the third color it works however it then gets stuck and throws up a runtime error '1004' Application defined or object defined error. I did get around this before by recording macros and effectively sticking them together but it hasn't worked on this occasion...... any ideas??? (I hope I'm not breaking any rules by pasting the code directly in?)
View 6 Replies
View Related
Nov 24, 2005
This is my problem:
A list with numbers in column A, these numbers can be used over multiple rows, sometimes 3, 5 or more/less.
Every time this number changes, the color for the entire row should flip between yellow and no color.
Sequence of the number in column A is not always odd / even. I tried various suggestions regarding this topic but I do not get it straight.
View 9 Replies
View Related
Oct 19, 2006
I have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting:
Formula is = MOD(ROW(),2)
My Second Condition is
Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.
View 7 Replies
View Related
Oct 17, 2007
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies
View Related
May 19, 2009
I am using the following formula on a conditional formatting to color cell A1 based on cell N5 date =$N5=TODAY()-2
The problem is that I need this formula to consider only weekdays, how can apply formula weekdays to my formula?
View 6 Replies
View Related
