Conditional Formatting Based On 2 Criteria

Aug 10, 2006

I am trying to figure out how to set up a spreadsheet, that comprises a list of open-entries, wherein the user will insert their information and a % out of 100 assigned to them; the entire form, in the end, cannot go beyond 100%, so each entry has to be done correctly...

BASICALLY my problem is that I am trying to set up the " Total %" cell, which is already assigned the formula "=SUM(D5:D16)" (D5 through D16 being the cells in which %'s are assigned) so that it will auto-populate with the appropriate color/error if the %'s are either insufficient (short of 100%) or exceeding (above 100%). What I had been trying to set up (and have successfully set up in other spreadsheets, however under slightly different circumstances) is a Conditional Format in which the first (with the color Red for "exceeding") was " =If(SUM(D5:D16)>100,SUM(D5:D16),SUM(D5:D16))

" So regardless of exceeding, exactly met, or insufficient totals, it would post the total entered, BUT assigning the color red, so that IF it did, in fact, total higher than 100, it would appear red. A similar formula would be applied to Green (reflecting "on target") in which the formula would be " =If(SUM(D5:D16)=100,SUM(D5:D16),SUM(D5:D16))"

Now the problem is: this is not working for the SUM of the cells. In my previous experience with Conditional Formatting and color assigning results, it had been with the AVERAGE of two columns of cells.(worked without a hitch.)

ADVERTISEMENT

Conditional Formatting Based On Two Criteria (Using VBA)

Apr 7, 2014

how 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.

View 2 Replies View Related

Conditional Formatting - Based On Formula - If One Of 3 Criteria Are Met Then...

Jun 23, 2009

I am drawing a blank. I want to use conditional formatting in cell b2 that will outline the cell(I know how to do that) based on a formula. The formula I need would determine if cell a2 is 26,27, or 28. Or function just returns true/false. I would rather not use 2 formulas.

View 2 Replies View Related

Conditional Formatting Based On Multiple Text Criteria?

Mar 1, 2014

I'm trying to set up conditional formatting in a spreadsheet where there are 3 possible correct letters that can be entered into a range of cells and if one of these letters is not entered then I would like the cell to be highlighted.

To be more precise, it's an accommodation spreadsheet and in each cell if someone needs a room for that particular night (each night is represented by a separate column) then a letter is used to indicate what type of room they require i.e. "k" is a king room, "s" is a Suite and "t" is a twin. I don't want to force people to have to select from a list which would be the obvious solution to this as it is simply too cumbersome for the amount of data entry we do.

However i want to ensure that if someone accidentally enters anything other than t,k or s then the cell is highlighted. We work in large spreadsheets covering months of dates so to manually have to find any errors is hugely time consuming.

View 10 Replies View Related

Conditional Formatting - Highlight Row Based On Cell Criteria

May 19, 2013

I need a macro that will highlight a row based on a cell criteria. I have used conditional formatting but there is a lot to format and has dramatically slowed the sheet down. However, I know nothing about macros/vbas.

E.g. If it states JI sent then I want the whole row to be highlighted light green.

App form needed = peach
Waiting list = light blue
Cancelled = red font strikethrough
DNA = purple font strikethrough.

Also - it could well be that in the row it has App form received, JI Sent and DNA all in 1 go. So ideally I would want that row to be highlighted light green with purple font strikethrough.

Booking Status
JI Status
Date JI Sent
Attendance
Cancellation date
Chargable / non chargable Cancellations
Chargeable / non chargeable DNA
Date taken
Time taken
Event Title
Start Date
Time

App form received
JI Sent

[Code] ......

View 3 Replies View Related

Conditional Formatting - How To Shade Cells Based On Meeting 2 Criteria

Jul 19, 2012

I have a gantt chart that was built in excel. I need to shade cells in the timeline based on persons title under each project.

I can provide a snip of what the layout looks like for a better visual. I want the PM's Green, Supers Yellow and Persons Reporting Orange.

To get the green bar shown is using formula: =SEARCH("Project Management",$E17) , The issue with this is it fails to consider the start finish dates. For this particular project it works but when the formatting is "dragged down" the issue arises.

View 9 Replies View Related

IF Statement Using Formatting Criteria (NOT Conditional Formatting)

May 5, 2009

Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:

if(A1=blue background,"Yes","No")

or

if(A1=red text,"Yes","No")

etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.

View 5 Replies View Related

Conditional Formatting Based On Another Cell Conditional Format

Mar 20, 2013

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???

View 3 Replies View Related

Conditional Formatting For Dates (x3 Criteria)

Jan 31, 2013

I'm desperately trying to work out conditional formatting for different dates.

I have =TODAY() in F3

In the remainder of column F (from F19 to F625) I have either blank cells or varying dates (dating back to 2011 and dates going forward into 2014).

I need to Red/Amber/Green the following:

Green = any dates which are 45 days or more in the future from today's date
Amber = any dates which are between +1 and +45 days in the future from today's date
Red = any dates which are today's date or a date in the past

I can get one or two of the criteria working i.e. Green and Red, but not the Amber. Is one overriding or ignoring another?

View 2 Replies View Related

Conditional Formatting And List Of Criteria

Jul 16, 2007

I have a column of information. The first cell has the record number. Then there are criteria the record must meet. If any of the cells have N for nonCompliant, i would like for the record number to change colors. If it were only one citeria, i think i know how to do that, but because there are as many as 10 I'm not sure how to make it change colors for all ten unless i do it one cell at a time.

Also there are several thousand columns on different worksheets, how do i apply the conditions to each column without doing it manually?

View 10 Replies View Related

Multiple Criteria In Conditional Formatting?

Jan 5, 2012

Start dateEnd Date01/01/201101/01/201102/01/201101/01/201110/01/2011

The above is the result that Im trying to get and I just cant work it out,

Basically,

If B is Blank Format Red
If B is populated and A minus B is less than 5 No format
If B is populated and A minus B is Greater than 5 format Red

-- Im sure its easy but I just cant get it

View 6 Replies View Related

Conditional Formatting With 3 Criteria For Each Color

Jan 19, 2012

I need to assign 3 critera for Red and similarly for Green 3 Criteria ..

For ex: Red:"3yr"≤40%

View 9 Replies View Related

Add Conditional Formatting Criteria With Code

Jul 18, 2007

I recorded some code and cleaned it up to apply a conditional format

Sub condi_format_I()
Dim wbBook As Workbook
Dim ws As Worksheet
Dim rng As Range

Set wbBook = ThisWorkbook
Set ws = wbBook.Worksheets("Summary")
Set rng = ws.Range("C4:P52")

With rng
.FormatConditions.Delete
.FormatConditions.Add xlExpression, Formula1:="=C4<0"
.FormatConditions(1).Interior.ColorIndex = 45
End With

End Sub

I would like to apply a custom color index to all cells in range C4:P52 if the value is less than zero.

But when I execute the macro, many cells receive the custom format that do notr match the custom format. Other cells that should receive the custom format do not

View 3 Replies View Related

Conditional Formatting Criteria From Another Worksheet

Aug 24, 2007

I have a worksheet titled "detail" containing 3 cells which automatically and independently change colour subject to their proximity to various dates. On a new worksheet titled "overview" in the same workbook I would like to have one cell which changes colour if any of the 3 cells in the other worksheet are triggered.

View 2 Replies View Related

Conditional Formatting To Return The Following Results If The Criteria Is Met

Jun 19, 2009

I have a conditional format that does not seem to be working for me. Cell B2 has a drop down optionSelect, No, Yes); Cell B3 is supposed to be conditionally formatted to return the following results if the criteria is met:

If B2 is equal to No or Yes then colour should become Yellow
If B3 is >0 then colour should become Blue

The problem is when B3 is greater than 0 it does not change the cell colour to Blue.

B3 Conditional Format #1 is =AND(ISTEXT(B2),B2<>"Select")
turn background to yellow
B3 Conditional Format #2 is =AND(ISNUMBER(B3),B3<>0)
turn background to blue

See attached for spreadsheet with conditional formats

View 3 Replies View Related

Multiple Criteria Per Conditional Formatting Rule

Sep 9, 2009

Is there a way color a cell red if the the text is "Z" or a number between 0.5 and 10

Or if the cell contains a "Z" or a "C" ....

View 13 Replies View Related

Multiple Criteria Per Conditional Formatting Rule..

Sep 18, 2009

how to adapt the solution so it would work in multiple cells. Since I was told to start a new thread, here it is. I want to format a cell based on its contents, If it has one of three entries, it should be greyed. I then want to use the conditional format across the whole page, but the solution given in the other thread refers to a single cell, how can I do this without that reference?

View 2 Replies View Related

Conditional Formatting Duplicate Value With Multiple Criteria?

Aug 21, 2013

formatiing a cell, e.g. change color to RED, in the event the value is a duplicate. To make it clear, I need to show that if the Serial Number of a product just entered is a duplicate and should change color when a combination of conditions is true. i.e. if a "part number" AND the currently entered "serial number" already exist, the entered serial numbers should change color.

View 3 Replies View Related

Conditional Formatting If Cells Match Criteria

Sep 13, 2006

I would like to have excel check if eight of ten of cells in a row are above a certain criteria, then if they are format the tenth cell to be red...so excel checks the ten cells above and if eight of them are above a certain number then that cell shades red.

View 6 Replies View Related

Exceeding 3 Criteria Limit In Conditional Formatting

Jun 6, 2008

The code segment from "Get Around Excels 3 Criteria Limit in Conditional Formatting" works perfectly if I type the variable directly into the cell, but not in a range of cells. That is to say, if I copy the source cell to a range of cells I receive a "Run-time error 13: Type mismatch" error". Is there a way to get around this? This is the code segment I've tried in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("b2:q53")) Is Nothing Then
Select Case Target
Case "IBBCH", "ibbch"
icolor = 36 'light yellow
Case "OBBCH", "obbch"
icolor = 34 'light turqoise
Case "OBBRDG", "obbrdg"
icolor = 35 'light green
Case "LNCH", "lnch".........................

View 3 Replies View Related

Apply Conditional Formatting To Cells That Meet Certain Criteria?

May 19, 2014

Column b in sample is conditionally formatted based on it's values. I want to also apply that same formatting to the person's name in the chart in D2:I9. For example, Jeff is in bottom 50% so cell B2 is shaded red with red text. I would like to apply that same red shade and red text to all the cells in my chart that say Jeff. Also, as example, all of the cells in my chart that say Kelsey would be formatted with green shade/green text and so on...

View 1 Replies View Related

Excel 2010 :: Conditional Formatting With Multiple Criteria?

May 16, 2013

I am trying to do a conditional formatting based on the result in one cell but it needs to add up multiple cells first to determine which conditional format it needs to use. I am not sure whether I need to do a formula conditional format or whether to use cell is greater than with =sum() in it. I have tried both but neither seem to be working correctly. I have attached an example of the spreadsheet and the conditions I am trying. I am using 2010 version.

Basically, the closing amount for Monday in prod 1 (cell c4), needs to display in red if sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4) & Fri (R4) is greater than that value or display in amber if the sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4).

I need to complete the same for the whole column for Monday (col C) for each appropriate product. Then the same for column B but this will sum different cells but the principal will be the same.

View 3 Replies View Related

Formula Within Conditional Formatting To Color Cells Meeting Criteria

Aug 25, 2009

I need to be able to get a macro to do this. I recorded the macro. The conditional worked, but when I cleared it and then ran the macro, it highlighted all of the wrong stuff. Here is what I want to do: In column(AJ) I have cities with Prefixes or not In another column (AV) I have Criteria1 and in yet another, I have criteria2 (CB)

City
(AJ) Criteria1 Criteria2
SCRNRTH_SCRM1 FALSETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE
A9_SCRNRTH_SCRM1FALSETRUE
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
SCRNRTH_SCRM1 TRUETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE
SCRNRTH_SCRM1 FALSEFALSE
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
A9_SCRNRTH_SCRM1TRUETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE....................

View 6 Replies View Related

Multiple Filters Or Advanced Search Criteria Not Working In Conditional Formatting?

Jul 18, 2014

I have created a spreadsheet that will show me where people are working on what day, etc. however i want to be able to filter by week to create a list of say 2 particuar shifts - in this case "syl ld" and syl n so that the spreadheet would show the people who are working these shifts and I could print out. I have tried Multiple Filters and Advanced search but cannot achieve what I require

View 3 Replies View Related

Sum Based On Conditional Formatting?

Feb 2, 2012

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.

View 3 Replies View Related

Conditional Formatting On Cell Based On Value

Jul 29, 2014

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 Related

Conditional Formatting Based On Another Cells Value?

Aug 18, 2014

On 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 Related

Conditional Formatting Based On Cells?

Mar 30, 2014

I 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 Related

Conditional Formatting Based On A Value In A Different Cell?

Apr 9, 2014

I 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 Related

Conditional Formatting Of A Row Based On A Value In A Cell Of That Row

Feb 17, 2010

I 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.

View 12 Replies View Related

Conditional Formatting Based On Value Of Another Cell

Jun 4, 2012

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.

View 7 Replies View Related

Conditional Formatting Based On % In Other Cell

Feb 15, 2013

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

View 6 Replies View Related

Conditional Formatting Based On Percentage

Oct 5, 2013

I need a conditioning format formula. If cell A2 is less than 50% of cell A1, then highlight red.

View 2 Replies View Related

Conditional Formatting Based On Several Conditions

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

Conditional Formatting - One Cell Based On The Value Of Another

Jan 6, 2014

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 Related

Conditional Formatting Based On Value In Another Column

Feb 17, 2014

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.

View 5 Replies View Related

Conditional Formatting Based Upon Various Dates

Oct 7, 2008

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())

View 3 Replies View Related

Conditional Formatting Based On Now Formula

Feb 19, 2010

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...

View 3 Replies View Related

Conditional Formatting Based On Person Name?

Apr 6, 2012

I have uploaded my excel spread sheet.

[URL]

Viewing only displays half the written request, but I downloaded it, and it was ok

View 8 Replies View Related

Conditional Formatting Based On Two Ranges

Aug 13, 2013

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.

View 5 Replies View Related

Conditional Formatting Based On Cell Value

Apr 17, 2014

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.

View 5 Replies View Related

ADVERTISEMENT