Conditional Formatting: If A Cell Value In Column X Contains The Word "open", Format Row Background To Yellow

May 7, 2009

I need to set up some conditional formatting on my spreadsheet, however I am having a little trouble with one of my conditions. The requirement is:

- if a cell value in column X contains the word "open", format row background to yellow.

- if a cell value in column X contains the word "resolved", format row background to green.

- if a cell value in column X contains the word "moved" OR "closed", format row background to blue.

Now I have manged the first two on my own, using the conditional formatting tool and using the formula "=SEARCH("open",INDIRECT("X"&ROW()))".

However I am stuck on the last one. I tried...

=OR(SEARCH("resolved",INDIRECT("X"&ROW())), SEARCH("closed",INDIRECT("X"&ROW())))

However this doesnt work. I tried looking at adding VB script in but to be honest I am not a VB programmer and cant really spend too much time on this. fix the final conditional format so it run if the cell contains either "resolved" or "closed"?

View 5 Replies


ADVERTISEMENT

Conditional Formatting - Highlight Yellow If Cell Is Keyed In Instead Of Formula

Dec 17, 2012

I have a file where the cells are all formulas.

Employees are able to key in a number instead of using the number derived from the formula.

I want the cell to use conditional formatting without the use of macros, so it will highlight yellow if the cell is keyed in instead of a formula.

Is this possible? I have tried numerous things, but none seem to work.

Example:

Cell A6 has the number 24,248 derived from a formula.

Employee thinks the number should be 30,000 and keys that number in.

If it is 24,248, I want the number to have no fill, but if it is a keyed in number, I want it to be highlighted.

View 4 Replies View Related

Conditional Formatting (column H Changes To Yes I Want The Background Of The Whole Row To Change To Red)

Jan 6, 2010

I have a spreadsheet where column H can be either yes or no. When column H changes to Yes I want the background of the whole row to change to Red.

e.g. Cell H4 contains the word no. I then change this so that it says "yes". I want the background of row 4 to go red. so A4, b4, c4, d4, e4, h4 all go red.

With the type of conditional formatting i normally do i can only get cell H4 to go Red by putting cell value = yes as the condition. I cant for the life of me think of a formula or way round of doing this.

View 4 Replies View Related

Conditional Formatting Turns The Cell Background Green With 03

Aug 27, 2009

In a column in my spreadsheet i have to input a number; either: 51-61 and next to it is either m or o. so in Column A row 1 : 51m, Column A row 2 60m, etc Example:

51m
51o
55o
60m

I want to conditionally format this such that for 51 it turns the cell background green, for 52 red, 53 orange,etc. with a number having a specific color associated with it. 03' does not have a text choice.

View 5 Replies View Related

Count Cell In Colour Background (Set By Conditional Formatting)

Mar 25, 2013

I have been assigned a task to create one table by counting how many cells are in red, yellow, blue, white colour.

red: indicate the certificate which will expired within one month

Yellow: indicate certificate which will expired within 2 to 3 month

blue: indicate certificate which expired

white (without any colour): indicate certificate that will expired in the future which more than 3 month

The table shall consist of "non-expired cert" - red and yellow; expired cert and total cert.

View 6 Replies View Related

Excel 2002 :: Conditional Formatting - If All Cells Match Background In One Cell To Change To Green

Apr 7, 2014

Excel 2002

I have a simple spreadsheet and am trying to get some Conditional Formatting to work.

Cell1 = Name
Cell2 = 1
Cell3 = 2
Cell4 = 3

If cells 2, 3, and 4 all match I want the background in cell 1 to change to green.

I've tried (Formula Is =B1=C1=D1 green) with no luck.

View 5 Replies View Related

Conditional Formatting (particular Word In The Value That Is In The Cell)

Aug 4, 2009

Is there a way that I can Conditional Format a cell is it has a particular word in the value that is in the Cell?

I'm looking to CF where Cell Value is like Duties:

View 3 Replies View Related

Conditional Formatting - Highlight Any Cell Containing The Word If?

Jun 9, 2014

I want to write a macro for conditonal formatting

I want to highlight any cell containing the word "If "

View 3 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 - Use Info From 2 Columns To Format 3rd Column

May 24, 2014

conditional formatting in Excel. I have two columns with pertinent information. I need to know the following and format accordingly:

1. Is the number in column A positive or negative?
2. Is the number in column B less than 0.05 or 0.10?

I would then like Column C to just be highlighted a certain color depending on the combination... there are 5 possible combinations and I would like the cells to be formatted so that:

1. Positive and less than 0.05 - Bright yellow
2. Positive and less than 0.10 - Pale yellow
3. Negative and less than 0.05 - Bright green
4. Negative and less than 0.10 - Pale green
5. This "combo" just means the criteria wasn't meant... which is possible b/c sometimes Column A may have text instead of a number of b/c the number in column B is not less than 0.10. If either of these is true, I want the cell to remain blank.

View 5 Replies View Related

Conditional Formatting: Format 3 Columns Depending On Certain Value From Another Column

Mar 26, 2009

I want to format 3 columns in excel depending on certain value from another column.
By example: I have the columns A B C completed with some text. On the column D it will be the numbers 0 or 1. If the number is 0 the background color from A,B,C columns shall be Green, if the number is 1 the color shall be red.

I have tried in conditional formatting with the formula =$D$1=0 but the color of the columns are changing only depending on the value from cell D1. I don't know what is the correct formula. For column A1 I want to check the value from D1, A2 - D2, A3 - D3,...,An - Dn.

View 2 Replies View Related

Conditional Formatting And Change Its Background Colour

Jun 30, 2009

I'm trying to set a conditional formatting to do the following:

If A1 is greater than 1, A2 will change its background colour to green. So the format needs to be in A2.

Basically in A1 is a page number that we have reconciled our bank statement to (labelled as "64" or "65" and so on), A2 has the end of statement amount.

Every month end we do a bank rec, the green cell is a marker to show what point we reconciled up to previous month.

View 2 Replies View Related

Conditional Format Based On Background Color

Jun 5, 2008

I'm attempting to write a conditional format formula that will change the background color based on multiple color options on in a row. I have cell A:c as first, last and middle name. I utilize the rest 40+ cells that provides dates that are conditionally formatted to based on dates. I've approached this trying to reference the columns dates and cannot figure it out. Heres what I would like to do:
If any cell in from D:Z has a background color of red, yellow, green, and none it will change the background color in cells A:C. Its easy to reference one cell but not multiple. I've attached a sample of what I would like to do and I manually changed to cells A:C to show how I would like to show my results.

View 2 Replies View Related

Conditional Format - Hiding Text With Shady Background

Apr 18, 2007

I'm attempting to use Conditional Formatting to hide text where the background is shaded. When the background is white, I can just set the text to white and it is invisible - no problem. However, when the background is set to anything else, and I set the text to the same color as the background, Excel LIES to me. It pretends that the text is invisible, but it still shows up in Print Preview and when it's actually printed. The Custom Format ;;; doesn't help me, because I need the invisibilization only under certain funky conditions.

View 5 Replies View Related

Macro Extract Data Sheet2 Based On Background Yellow Color

Apr 19, 2014

I am looking some macro code to extract data based on background color (yellow color) from sheet1 to sheet2
for detail like this below :

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}

[Code] ........

View 5 Replies View Related

Conditional Formatting Based Upon Format Of Another Cell?

Mar 25, 2010

Is it possible to format a cell based upon the format of another cell? For instance, if the cell to the left of a target cell is bold, is there a conditional formatting cell that can be used so that the target cell is bold as well?

View 8 Replies View Related

Cell Format For Conditional Formatting With A Formula?

Jan 25, 2012

I have a large spreadsheet with a number of columns that are set up for conditional formatting. For example a column of "BUY"'s and "SELL"'s where the "BUY"s are formatted with the default "light green fill, dark green text". I have other columns that are conditionally formatted using a formula. When I base the result on a formula I don't seem to have the option to use the default formats. Is it possible to use the defaults or maybe even how to customize my format to look like the default? It would look a lot better if I had a uniform red/green style throughout the sheet.

View 1 Replies View Related

CONDITIONAL FORMATTING TO VBA. Row Format Based On Cell Value

Apr 19, 2008

I have set 3 conditional formatting rules where the row from column A to K will change depending on the value on column L.

Selection is equal to A3:K100, conditions are similar to:
- Formula is =$K3="PIP"

Then for formatting I chose WHITE BOLD FONT & BLUE SOLID PATTERN, for example.

Conditional formatting only allows 3 conditions. If this can be replicated
using VBA, then you can set "n" conditions, correct?

View 9 Replies View Related

Conditional Formatting - Blank Cell Gets Unwanted Format.

Oct 31, 2008

As a pert of a spreadsheet I have 2 columns (M5:N400) of 400 cells deep. Each cell in these columns contains a formula that counts days, when appropriate. They can end up showing a value of blank, a number between 0 and 5, or any number greater than 5. (But very rarely greater than 30)

If the number is greater than 5 then it means that it has exceeded it's target time and I wish to highlight this with conditional formatting (CF). I have a CF on all cells in both columns that says.

If "The cell value" is "Greater than" "5"... then
format the background colour to a pale yellow.

It works, but doesn't work.

If the cell value is 0 to 5 it's unchanged. Good.
If the cell value is >5 it changes. Good.
BUT, if the cell is blank it also changes. Bad!!!

I assume it is because it is looking at the fact that there is a formula in the cell, even though the displayed value is "". So I tried a second level saying that if the cell is equal to "" then no format, but it still changes.

View 4 Replies View Related

Using Conditional Formatting To Format Range Based On One Cell

May 20, 2009

I am working with the Conditional Formatting, which is fine for one cell. Here is what I am trying to do: IF cell in $A1 = 1 then bold $B2:$M2 and apply solid line border to top of cell ranges. I have tried conditional formatting but it only formats the cells in column A. And I can't seem to find a BOLD statement for the cell formulas.

View 2 Replies View Related

Open Word Doc And Format?

Jun 5, 2012

I need to open a blank word doc, paste a spreadsheet onto it, format it and filter out some keywords. All from excel of course.

1) Access the internet web page - done
2) Open the excel ss - done
3) Open word - not done
4) Paste the ss onto word - not done
5) Format the doc - not done
6) Filter out rows containing keywords - not done

Just to get started I tried this code to open an existing word doc but it doesn't work

Code:
Private Sub CommandButton1_Click()
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Open("C:BShift.doc")
End Sub

It says file cannot be found. There is a file there named BShift in word 2010.

View 5 Replies View Related

Conditional Formatting Cells To Show No Format If Cell Contains A Date

May 3, 2014

How can I condition format a cell to show no format if the cell contains a date

Please see the attached book1.xlsx for more info. Book1.xlsx

View 7 Replies View Related

Every (nth) Cell In Column Is Yellow?

Dec 20, 2011

One cell in my spreadsheet determines the number of parts in a box. What i want to do is use conditional format - if there are 7 parts in a box then every 7th cell in that column is coloured yellow. If there are nine parts then every 9th cell in the column is coloured yellow Etc...how to do this?

View 9 Replies View Related

Word Search Finder Using Conditional Formatting

May 24, 2014

How to search words puzzle quickly using conditional formatting in excel?

View 7 Replies View Related

Worksheet Events: Use A Worksheet Event To Change The Background Of The Current Month Two Columns In The Range To Yellow Color

Jun 12, 2007

In cell A1, I have the month number (eg, 1, 2, 3,). The month number reflects current month and will automatically change with every month. For example, right now itís 6, next month it will automatically change to 7. Each two columns in Range A10:X20 represents the data from January to December. I want to use a worksheet event to change the background of the current month two columns in the range to yellow color and the two columns in the range will be visible when I activate this sheet.

View 3 Replies View Related

Formatting Cell To Show Dd/mm/2011 When Format Whole Column?

Feb 8, 2012

I need to enter dates for the previous year, is there any way of formatting the cell to show the dd/mm/2011 when I format a whole column?

View 2 Replies View Related

Conditional Format Column / Cell That Don't Meet Criteria

Mar 19, 2014

I am trying to conditionally format cells in column I (named range of ETID).

I want the cell to change shading if the cells do NOT meet this criteria:

Whole Number10 digitsstart with '0' or '1'I have this but it isn't working:

=AND(OR(LEFT($i2,1)="0",(LEFT($i2,1),1)="1"),OR(LEN($I2)<>10,($I2)<>""))

View 4 Replies View Related

Conditional Format Entire Column Based On One Cell Not Having A Value

Nov 22, 2013

I need to make an entire column formatless if the cell in row 3 contains no text. I want the whole column to just appear like a normal formatless column, but if there is text in the row 3 cell, I want it to be the way I have it formatted right now.

View 1 Replies View Related

Sum The Sales Revenue Column Of The Attached Per Month Into The Yellow Highlighted Cell @ The Bottom

Sep 30, 2009

is there a formula i can use to sum the sales revinue column of the attached per month into the yellow highlighted cell @ the bottom?

View 2 Replies View Related

Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?

Feb 9, 2009

I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.

View 2 Replies View Related







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