For my example, in column B I have a list of guest names. These names will repeat based on their entries.
In column C I have amounts next to their name.
If the total of all amounts next to their name equal $1,000 or more I want all the rows that their name is in to be highlighted.
I attached an example.
I would really appreciate if someone could help me with the following problem:
I wrote this code, but for some reason it doesn't work. I am completely new to VBA and have no idea what went wrong:
I have a value in column H7 as 2000.
I have columns M to X labeled Jan to Dec. I want to highlight in yellow values in the row M7 to X7 if they are greater than 2000!
Then, I need to copy this conditional formatting down the columns under M to X, but under column H the values will be different, but I still want the same results, values greater than the value under column H then highlight in yellow.
I thought I had the answer but when I tried to copy down, it was making cells yellow that were clearly less than the column H value.
I am putting together a worksheet with all my teams tasks. I have columns A:U and Status is column "M". What i want to do is when ever i select Complete from the list in Column "M" i want to the row to turn Green.
I tried the following:
Selected the Range
From Conditional Formatting menu selected Manage rules
Selected new rule then Use formula to determine which cells to format
formula i used =$M$2=complete and selected green from the format menu
BUT nothing is happening. What am I doing wrong.
BTW the Values in Column are Complete,Development, Not Started Research, QA/UAT. If I can conditionally format based on these selections that would be even better.
I have a column of cells containing dates. I need to change the colour of any cell which is 7 days older than todays date. I would like to leave the header and any blank cells unchanged. Every formula I've tried has changed the enire column, or if I selected a range, the entire range.
View 5 Replies View RelatedI'm trying to highlight cells a certain color based on the value of the cell 1 column to the left. How would I be able to do this in conditional formatting?
Example)
Highlight the cells in "Visit 2" column if it is greater than the "Projected Visit 2" column.
NameProjected Visit 2Visit 2Person 124-Nov-0820-Nov-08Person 226-Jan-0927-Jan-09Person 32-Jan-082-Jan-08Person 430-Dec-0829-Dec-08Person 46-Nov-0830-Oct-08
I'm working in Excel 2010.
I setup a table with a column titled "Type". Each value under column "Type" defines the type of row it is: "Section", "SubSection", "SubSubSection"
I wish to create a set of conditional formatting rules that apply to data rows in this table, each rule controlling how cells within a whole row should be formatted, according to the value found in "Type", for that row.
Example:
Rule1: [@Type]="Section" -> fat red line on top of cell.
Rule2: [@Type]="SubSection" -> thin black line on top of cell.
Rule3: [@Type]="SubSubSection" -> dashed black line on top of cell.
I can't seem to make this work.
How can I leverage the nice column names, and the "@"-this row designator, within a table to create conditional formatting rules that apply to all the cells within a row, in the same way one can refer to in table formulas?
I am trying to apply a conditional format to an entire row based on the presence of any value in the first cell of that row to format to a certain color. If there isn't something present in the first cell, I don't want it to change.
Everything I keep trying ends up highlighting the entire workbook.
I would like to apply the rule to the entire sheet but only have it highlight the rows which have a value/text in the first cell of each row.
I am assuming I should use the formulas selection for conditional formatting but I'm unsure of what formula would apply to this type of request.
I would like to total numeric amounts based text in another column.
For example, I have a bunch of rows in "column F" with the same text (company name) and in "column I" i have numerics. Is there a way to total "column I" based on the same text of "column F"?
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
when the largest number in column B the hotel in column A should be in bold.
So in excel language IF(Number in B Is Max display corresponding hotel in column A as BOLD. But I can't figure out how to do this.
You can see here on the image:
additionalimage.gif
I currently have a spreedsheet which has a conditional formatting cell where if the figure is above 1 is green and below red what i am trying to do is get a sum to calculate based in its color?
I.E. I have a value in cells F7+F25+F27+F29 i then have a value in F17 but only want this to be included in the total calcualtion if the figure in E15 is equal/above the value of 1.
In Excel, I want to do conditional formatting on a cell based on the value of another cell. For example: in cell A1, I have text. I want this cell to have a grey background if the value of cell B1 is less than or equal to 1/31/14 and to have no fill (default) if it is greater than that date.
View 8 Replies View RelatedOn the attached worksheet I'm looking for the squares in F4:F6 to be coloured corresponding to the colour names in D4:D6 using conditional formatting. I just can't get it to work today.
View 1 Replies View RelatedI have a worksheet which has the years 2001 to 2013 on in row1. I am trying to find a way of highlighting the entire row in green if a specific year contains an "a" (Marlett, tick). The complex part is that I would like the specific year to be determined by an entry input into cell A2 on sheet 2. See Attachment for reference.
View 2 Replies View RelatedI am working in a table where I would like the whole row to highlight based on the value in one of the cells. These rows of data will be updated regularly, so the formatting has to change with the data.
View 13 Replies View RelatedI want to create a conditional formatting based on a cell in a row. If the value of that cell is greater than or equal to $20 the entire row would be highlighted with a color.
the main thing is that the cells in the rows have if formulas that supple the data for the cells. The cells will be blank if nothing is to be displayed but they will still contain the formula.
I want to format a cell conditional on the value of another cell. I have read numerous threads on the topic and no matter what I do, it doesn't seem to work for me. I've made it a simple as possible by creating a new workbook and entering:
A1: 5
B1: 2
C1: Some random text - this cell has the conditional formula: ="A1>B1"
However, the cell doesn't get formatted. Here is a screenshot and here is the simple workbook.
I want to color format a cell based on a percentage value in another cell but I just can't get my head around how to solve it. I've attached a file that explains it better.
E.g.
The formatting for A1 should be something like:
If A2 is
> +/- 10% RED
+/- 6-10% YELLOW
+/- 5% GREEN
Attachment 214464
I need a conditioning format formula. If cell A2 is less than 50% of cell A1, then highlight red.
View 2 Replies View RelatedI 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)
I am attempting to apply conditional formatting such that cell B3 is green if it is greater than or equal to cell B2, and red if less than cell B2. Then to apply this conditioning across the row of data (i.e. C3 >= C2 green, C3 < C2 red; D3 >= D2 green, D3 < D2 red; E3 >= E2 green, E3 < E2 red ... etc.). Needs to be formatted such that if I change the value in cell B2, the colour of B3 is automatically updated.
View 4 Replies View Relatedhow to create VBA code for conditionally formatting a column of dates that have expired. My unique challenge is that I first need to associate the item attached to the date with the expiration date of a particular group. Therefore, I have to first match the Item label with the appropriate group, and then I need to evaluate the expiration dates of both the Group and the Item. If the Item expires before the Group expires, then I need the Item date to be formatted in bold and with a red font.
Attached is a sample spreadsheet.
Thoughts about how to accomplish this with VBA? By the way, VBA is the necessary approach due to the fact that the data is updated several times per day, and all existing data is erased prior to uploading new data.
I have a spreadsheet I'm trying to make which will have various cells change color based upon the date entered compared to today's date. Here are the details of it, and what I've tried to do so far:
Various safety tickets that employees acquire have a 3 year expiry date on them. What I want to do is be able to enter in the issue date, and then have the conditional formatting color the cell that contains the date based on the following criteria:
Green = More than 6 months remaining
Yellow = Between 6 months and 30 days remaining
Red = 30 days until expiry
Black = Expired (I wanted to eventually add this one, but Excel 2003 was only allowing me to add 3 conditional formatting conditions, so I just went with the 3 colors. I'm getting Excel 2007 later today, so I might be able to add more with that)
Now here is what I have so far:
3 years = 1095 days, 2.5 years = 913 days, 2 years and 11 months = 1065 days
N3 = 913
N4 = 1065
Conditional Formatting on cell C21
Condition 1 - color Green
=$C21+$N$3>=TODAY()
Condition 2 - color Red
=$C21+$N$4<TODAY()
Condition 3 - color Yellow
=AND($C21+$N$3<TODAY(),$C21+$N$4>TODAY())
My attached spreadsheet is for tracking when certain tasks were completed on each case and figuring the number of days that it takes to complete these tasks.
The task I am needing help in formatting is the Corrections Received field (column E). We need to track the cases that do not have corrections received within 7 days of the changes being requested (Out for Corrections, column D). The issue is that there would not usually be a date entered in the Corrections Received column when we need to look at those cases...because they would not be received yet. In order to not get an extreme default value (like the -40181 or 40922), I entered the =Now() function in column E (except for first case as an example of what this would look like otherwise).
What I was hoping to be able to do is use conditional formatting to look for that Now() formula. If the formula is present, the cell would be highlighted. If a date were entered, even if it was today's date, the fill on that cell would be removed. That way we could tell when the displayed date is a true date and when it is a placeholder until we receive the information.
Currently, it either highlights no matter what the cell includes or it doesn't highlight anything...
I have uploaded my excel spread sheet.
[URL]
Viewing only displays half the written request, but I downloaded it, and it was ok
Any way write a conditional format formula to shade a cell based on two ranges.
Here is what I need:
cell b4 should be colored in if n4:xfd4 = today's date AND n3:xfd3 = "Time Off". The part I am having trouble with is that it should only shade if today and time off are in the same column.
Here is an example:
This one should shade B4(note: Today is 08/13/2013):
time off
late
08/13/2013
08/10/2013
This one should not:
late
time off
08/13/2013
08/10/2013
This is what I came up with so far, but it shades the cell if either show up in the entire range:
=AND(MATCH($G$1,$n$4:$XFD$4),MATCH($H$1,$n$3:$XFD$3),1)
Note: g1 contains =today() and h1 = "Time off"
So formula is wrong.
I am manually formatting column J depending on what data is entered into column K. If the number entered into the cells in column K are equal to or less than what is in column J, I format the data in the cells in column J with the green you see above. If the data entered in the column K cells are greater than column J, I format it in the red shown above.
So, how can I have Excel do this for me, format column J cells depending upon what is entered into column K cells? The caveat here is that I also don't want any formatting to occur until data is entered into column K.
I am trying to apply conditional formatting to one cell based on the values of cells in a row.
This is what I have at the moment
E3 = TODAY()
E4 = TODAY()+90
If any date in the row falls between E3 and E4 i would like the font in another cell to turn red (in the example below A11 should turn red if any date in B11:J11 is between E3 and E4).
This is the formula I've been trying:
=AND($B$11:$J$11>=E3,$B$11:$J$11=E3,$B$11
I am trying to use conditional formatting to highlight items that are either past due or coming due soon. The data to be evualuated is in a report that I extract from another system and run bi-weekly or monthly. The dates included in the report represent the date in which something was last updated, certified, tested, etc. All dates expire one-year from when they were last completed. The colors that I want are as follows:
If something is past due (date shown is more than 1 year old) or due within 30 days = RED.
If something is due within 31-60 days = YELLOW
If something is due within 61-90 days = GREEN
So, if an item on a report is showing it was "LAST UPDATED" on 2/21/2007 it should show up RED because it is due within the next 30 days.
If an item is not expired (less than 1-year old) and falls outside of the defined 90 day window then it should just remain as is with no formatting.