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


ADVERTISEMENT

Excel 2007 :: Conditional Formatting And Adding Text To Cell Based On Multiple Cells

May 31, 2013

I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.

This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.

I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.

I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.

View 3 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 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 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.)

View 4 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 - 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

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

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

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

Multiple Conditional Formatting On Text

Aug 7, 2006

I need to employ conditional formatting to text on a spreadsheet - but using more than the 3 colours provided from the Format>Conditional Formatting menu.

I recently purchased the ‘Excel Hacks’ (100 Industrial-Strength Tips & Tools) book by David & Raina Hawley, Hack #92 details how to get around Excel’s 3-criteria limit for conditional formatting using VBA, but this solution only describes multiple formatting on numbers, not text. For example, if each letter of the alphabet were entered into separate cells, I would like each to appear in a different colour automatically.

View 3 Replies View Related

Conditional Formatting If Multiple Cells Contain Same Text

Dec 30, 2012

I want to conditional format based on all of the following cells contain the same text as AT19 or not.

A19 H19 O19 V19 AC19 AJ19

What is my formula?

View 1 Replies View Related

Conditional Formatting Based On Text Of Another Cell

Jul 30, 2009

I would like to have cells in a certain column turn green if the word in column J of the corresponding row = Not Changed".

View 4 Replies View Related

Conditional Formatting Based On Text Values.

Mar 9, 2009

scenario: Column H needs to be formatted to green if the text in Column F does not equal "Dog". Dog could be either DOG, Dog or dog. I know how to get it done with only dog, but this one is a bit tricky.

View 2 Replies View Related

Conditional Formatting Based On Multiple Inputs?

Feb 19, 2013

I'm working on a fairly complex spreadsheet where I want to pull conditional formatting into an entire row. I have a column in tab1 where I have a bunch of beer styles listed. On tab2, I have a 2 columns: a complete list of styles with a 'yes' or 'no' in the next column based on certain criteria. What I'd like to do is have the column in tab1 highlight if that style in tab2 has a 'no' in the column next to it.

View 3 Replies View Related

Conditional Formatting Formula Based On Text In Two Cells?

Jan 8, 2014

I need a formula for conditional formatting which highlights a row if A has the text "Ex" and B has "Paid".

View 2 Replies View Related

Conditional Formatting Based On Text In Adjacent Cell?

Feb 16, 2014

A2:A5 contains different dollar amounts, a6 is the sum of those amounts

In column B (B2:B5), i place a "p" beside the amount in column A once it has been paid.

I want to:

a. conditionally format the amounts in column a to turn green once I put the 'p' in the adjacent column

b. conditionally format the sum in A6 to exclude amounts that have been paid, and only display the total amount of the unpaid lines.

View 2 Replies View Related

Traffic Light Conditional Formatting Based On Text?

Nov 4, 2013

I'm trying to create a dashboard with traffic lights displaying the status of a task. My manager wants a to be able to pick a color (Red, Yellow, Green) from a drop down menu and then have that be displayed as a red, yellow or green traffic light. Using the conditional formatting option, I'm only able to use numerical values or a formula. I'm hoping there's a way to either create a drop down list with the 3 different colored traffic lights or a way to use VBA to have the conditional formatting search by text instead of numbers.

View 2 Replies View Related

Conditional Formatting Based On Color Or Text Of Another Cell

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

Conditional Formatting Based On Multiple Check Boxes

Sep 19, 2013

Basically I have a list of companies each with five corresponding check boxes in different cells. I want the cell with the company name in (or just a symbol in the cell) to appear Green, Amber, Red when 5, 3-4, 1-2 of the boxes are checked respectively.

View 3 Replies View Related

Conditional Formatting Multiple Number Values But IGNORING Text?

Sep 6, 2013

I have a spread sheet where there are multiple rows of price quotes from different suppliers.

Some suppliers have quoted, some however have not and there is a "#N/A" or "no bid".

SupplierA
SupplierB
SupplierC
SupplierD

1.3
No Bid
2.3
No Bid

How can I get a conditional format to check each row for the lowest numeric bid, highlight it but ignore the text?? What I have tried so far doesn't work.

=G31=MAX($G$31:$J$31)

This does not seem to work, I don't know how to exclude the text values from the MAX function.

View 14 Replies View Related

Conditional Format Based On Multiple Criteria

Feb 16, 2010

I have a spreadsheet that requires me to manually hunt down records that match based on several different criteria. this is very time consuming and inefficient. i would like to find a way to highlight certain records that match based on several different fields. i have posted an example spreadsheet that also has a more detailed description of what i am trying to do; which is located in the second sheet.

View 9 Replies View Related

Conditional Formatting - Duplicate Rows Based On Multiple Columns?

Apr 8, 2014

I'm looking for a Conditional Formatting formula that will check two columns before highlighting the duplicate rows. I need it to be conditional formatting because I know nothing about writing macros or vba (what-ever that is?). Data is entered into Columns A, B, and C. I need to check both column A and C before it highlights the duplicates, based on those two columns. (The format only unique or duplicate values checks only one column.) I have attached an example, but this is just an example, as I have hundreds of lines to go through on the original. (For this example, Row 2 and Row 7 are the duplicates I need highlighted.)

View 3 Replies View Related

Hide & Show Rows Based On Multiple Conditional Formatting

Aug 6, 2009

I am trying to hide/show entire rows of a range based on the conditional formatting in the row. I want all rows with at least one overdue training cell (indicated by a red cell) displayed, and rows with no overdue training hidden. The conditional formatting formulas vary greatly, but always result in a white (unchanged), yellow, or red cell. Here is a sample picture for reference:

[url]

The CF formulas vary based mostly on two major factors: the frequency of the requirement found in Column "C" (Monthly, Quarterly, Semi-Annual, or Annual Requirement), and the personnel's arrival on site or date of departure (wheels up) found in Rows("3:4"). Each training class has two rows. The first row indicates the last time the class was completed, and the second row shows when it is due next. Both rows have to be displayed/hidden based on the second row's conditional formatting. Here is the code I am using right now: ...

View 3 Replies View Related

Excel 2011 :: Conditional Formatting Based On Text Match From If Statement?

Oct 2, 2013

I want to highlight a cell if the text displayed from an IF formula is equal to the cell content.

CELLS BK10:BN10 are merged and have entered into them the following text "SELL"

Cells BJ20:BP20 are merged and contain the following formula which currently results in the cell displaying "SELL"

=IF(BM22<=-0.08,"SELL", IF(AND(BM22>-0.08,BM22 < -0.03),"NO INDICATION", IF(BM22>=-0.03,"HOLD")))

Cell BM22 is a percentage calculation of the differences between two different days of volume for this stock and that formula is: =(BH22/BH25)-1 Cell BM22 currently is calculating the result to be -65.65%

When I set up a CLASSIC Conditional Formatting using a formula (="If($BJ$20=""SELL""") to check the if the text in cells BK10 match the text displayed in cell BJ20 - I get no error messages and no formatting?

I am using Mac Office Excel 2011. I feel like I have tried everything including changing the Number selection type of the cells to TEXT. Nothing seems to works.

View 1 Replies View Related

Conditional Formatting For Entire Row Based On Presence Of Any Text / Values In Column

Oct 19, 2013

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.

View 3 Replies View Related

Conditional Formatting To Colour One Cell Based On Text From Range Of Cells?

Oct 22, 2013

what i would like to do is change the fill colour of D68 if the word Air appears with in D5:D65?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved