# Conditional Formatting To Change Colour Of Cells Dependent On Text Within Them

Sep 8, 2013

How to colour cells depending on text in other cells, for example,

I would like cells D26:AA26 to turn light red if the letters 'FSM' are in cell 'E26' even if other text appears in it, eg 'SA+/FSM'
I have been playing about with conditional formatting but can seem to solve the mixed text issue?

I have included an example below,

Do you use a "*FSM*" around the formula?

[URL] ...........

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

## Conditional Formatting: Change Colour

Feb 16, 2010

In cell A1 I have revenue projected & B1 I have actual revenue if the date in B1 is less than the date in A1, I would like B1 to change colour.

## Conditional Formatting: Change The Colour..

Oct 18, 2007

I want to say when CS2= same value as CS2 on the client options sheet (on the row specified in I2 sheet 1) change the colour.

How should this be tweaked to do that? I'm not getting it!

## Change Colour In A Cell - Conditional Formatting

Dec 4, 2008

I want to change the colour in a cell depending on the sum of two other cells.
ie
A3=1,B3=1
A4=2,B4=1
A5=1,B5=0
A6=2,B6=4

So i want if
A3+B3>2 THEN C3 TO HAVE GREEN COLOUR
A4+B4>2 THEN C4 TO HAVE GREEN COLOUR
A5+B5>2 THEN C5 TO HAVE GREEN COLOUR
A6+B6>2 THEN C6 TO HAVE GREEN COLOUR

SO IN THE END ONLY C4 AND C6 WILL HAVE GREEN COLOUR.

Is there any formula to do so with conditional formatting?

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

## Conditional Formatting To Change Colour Of Rows

Aug 13, 2009

I want to use conditional formatting to change the colour of the entire row when the date on A1 is any date in September (01/09/09 to 30/09/09).

## Conditional Formatting: Change The Cell Colour

Aug 21, 2008

I am trying to setup a monthly calendar.
What I want to do is have it so that if you type "apples I want some", the cell changes colour to green, and also the apples doesnt appear in the cell. So when you press enter, all you will see is a green cell that says "I want some"

I was wondering if there was a way in VB within the worksheet to make this happen?

## Conditional Formatting Colour Change Based On Dates?

Jan 8, 2012

I have a simple due date spread sheet for tasks that need completing at work. I am able to make the 'Due date' cell turn red when it is equal to or less than todays date, using conditional formatting. No problem.

I also have a 'Date closed' cell which is populated with a date when the task is complete.

What I am struggling to do however is change the red 'Due Date' cell to Grey once the 'Date Closed' cell is populated with a date. In fact I would like the whole row to be greyed out once the 'Date Closed' cell is populated.

I also have an 'Open / Closed' cell, which tells me if a task is open or closed, being chosen from a drop down I installed. Could this be used as an alternative to the 'Date Closed' cell. ie If someone selects 'Closed' in cell A4 the whole A row goes grey.

## Excel 2003 :: Conditional Formatting Not Working And Cannot Change Colour Of A Cell Or Writing?

Sep 23, 2013

Excel 2003

On one of our computers at work, Excel is acting especially strange. The conditional formatting in Excel on one of our computers is not working. In addition, we cannot fill a cell with any color or change the color of the writing. We can click a color for the letters and type it in, but immediately after another cell is highlighted, the writing goes black again. When we try to highlight multiple cells with the cursor, the cells are black instead of the normal color. This happens to not just spreadsheets that are new, but existing spreadsheets on our shared drive from only this one computer as well. No other computers are affected this way. All other computers show the spreadsheets fine and do not have any functionality issues.

## Conditional Formatting Query: Changes Colour When Certain Text Is Entered

Sep 10, 2009

I have a column of cells that changes colour when certain text is entered into them, ie "TIME" will make the fill colour brown. However, when other text is entered into the cell alongside "TIME", the conditional formatting doesn't change the colour of the cell. How do i get around this so it picks up on the "TIME" text even when other text exists alongside it?

## Conditional Formatting To Colour Cells

Nov 7, 2008

I'm designing a holiday chart in work, and am using conditional formatting to colour cells. The problem is I have more than 3 conditions. Consequently I need to use a macro.

## Use Conditional Formatting If 360 Cells Automatically Changing Colour Red

Sep 10, 2013

I have figure A1 column which total amount accounting at cells A31. I need figure 360 as a benchmark calculation perfectly been done. In this calculation 360 well off inside cells A18 although actual number is 363. If we use Conditional Formatting when had enough total in cells the achieve 360, cells colour will be transformed to red. What formula which need I used to change no matter cells on A1 to A30 if sufficient study 360 automatically cells changing colour to red

## Change The Colour Of Another Cell Dependent On Dates

Jan 18, 2010

I'm looking for a formula that will change the colour of another cell dependent on dates.

Say I have a date (or any data) in cell A1 I want cell A2 to be highlighted yellow, after 10 days I want this cell to change to red as it is overdue.

Finally when a date is entered into the yellow/red cell I want the colour to remove.

## Locking Text In Cells But Not The Ability To Change Colour Of Cells With Mouse Click

Mar 5, 2013

Locking text in cells but not the ability to change colour of cells

******** width="234" height="60" frameborder="0" marginwidth="0"
marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" id="aswift_0" name="aswift_0" style="left: 0px; position: absolute; top: 0px;">*********>

I have a spreadsheet where I can change the colour of a cell by clicking the mouse, I also have text in many of the cells.

What I need to do is protect (lock) the text so that no one can change the text in any of the cells, but I still want to be able to change the colour of the cells by clicking the mouse in that cell.

## VBA To Change Background Colour In Free Form Depending On Text In Cells?

Apr 3, 2014

I have a spreadsheet with several freeforms. I would like them to change backgroundcolour, if the content in a cell is equal to another cell.

Basically I would like my shape "Freeform1" to change backgroundcolour to RGB (0, 180, 0) if Sheet1.Range ("A1") = Sheet2.Range("D3").

If it's not the same content in both cells, I would like the freeform to remain unchanged, that's RGB (79, 129, 189).

This should happend automaticly, so I don't have to click the shape to make it happen.

## Conditional Formatting: Change Another Cells Color

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.

## Dependent Dropdown Lists And Conditional Formatting?

Jun 14, 2013

I have three drop-down boxes in 3 adjacent columns. Column 1 is free-choice, Columns 2 and 3 drop-downs are variable dependent on what is in Column 1. That works fine.

I have an issue with over-type but I can solve that with protection. Again fine.

BUT:

1. How do I make the user choose something i.e. not just leave the cell in column 2 or 3 blank by ignoring it (i.e. blank is an error but only after drop-down in column 1 is activated)

2. Also, if the user has completed the line (columns 1,2 and 3) and then changes column 1, columns 2 and 3 are now reading from the incorrect drop-down boxes (i.e. they are now in error but this is accepted and not flagged).

## Conditional Formatting - Change Value Font Color In Cell Based On Matching Text?

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?

## Code To Colour Cells Dependent On Reference To Adjacent Criteria

Jun 11, 2014

I have developed the following code to colour cells dependent on reference to adjacent criteria (in Col D).

Is there anyway I can simplify this code to avoid three separate "For each" statements?

Code:
For Each c In Range("E12", Range("e" & lastrow))
If c = "" And c.Offset(, -1) "" Then
c.Interior.ColorIndex = 7

[Code] ..........

## Conditional Formatting With Colour

Jul 17, 2009

Im trying to create a stock database based on a colour traffic light system. I have 2 set columns, the first is 'recommended stock' the second is 'actual stock'

When you paste in your requirements into the 3rd column it subtracts it from the acutal stock which tells you how much your recommended stock is short by.

What ive got as well is any stock above 1+ shows up in green (this needs changing - see below), anything below 0 shows up in red. What im struggling with is i want a rule that reads the recommended stock level, then flags it up yellow when the actual stock levels drop to 10% of full stock.

how to use the conditional formatting if that is the way.

## Conditional Formatting - No Data No Colour

Nov 4, 2008

On this particular formula but my colleagues have informed me that they require cells with no dates in to have no colour fill. Firstly, I have searched and tried myself with no success on stopping the below formulas from filling empty cells with red, they are as below;

Code below for 11 months after entered date:

## Conditional Formatting - Colour A Whole Row Of Data

Jun 8, 2009

I am trying to colour a whole row of data (in columns A through to AA) based on the numeric entry in column AB. The numeric entry is either 1, 2 or 3. I reckoned I needed to use the IF function, but as writing formula is not my strong point I am struggling to enter the correct data! The first row of data I wish to use is 3. I have tried the following but to no avail: =IF(\$AB3,1). =IF(\$AB3=1). =IF(\$AB3="1").

## Conditional Formatting Cannot Keep Original Colour?

May 26, 2014

I am having problems with conditional formatting,on sheet1 ,if column "n" is yes then it is coloured red,when I search database say f124 I would like all records for f124 to appear and keep column "n" red or even better the whole row red,it seems just to locate all records with "n" =no and all orange /brown colour

## Conditional Formatting: Set The Colour Of A Cell

Jul 11, 2006

I am using conditional formatting to set the colour of a cell. I now want to change the colour of the adjoing cell to the same as the conditional format cell. CF doesn't have this as an option

## Excel 2003 :: Font Size Dependent On Cell Value (not Using Conditional Formatting)

Nov 28, 2012

Excel 2003.

I am creating a heat map using two variables from a questionnaire: firstly the number of people impacted by a question; and secondly, the level of the impact. Respondents are asked to give these values in two adjacent cells. These are then multiplied to give a weight eg 1000 people x level 3 impact = 3000. I have used Conditional Formatting to colour the cell according to the impact level (0 = none - white (default), 1 = moderate - tan, 2 = significant - orange, 3 = high - red). What I want to do is then change the font size to give an indication of the number of people impacted, eg 1 - 1000 font 8, 1001 - 3000 font 12, 3001 - 8000 font 16, 8000 - 2000 font 24 etc. The size of font and colour will then represent the number of respondents and their impact.

I can't use Conditional Formatting as the value of 3000 could be derived from 1000 people at level 3 (red), or 3000 people at level 1 (tan). Is there another way of setting the font size depending on the cell value, eg by VBA?

## Conditional Formatting: Cells Filled By Red Until The User Enters Text In Those Cells

Jul 18, 2006

Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??

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

## Conditional Or IF - Formatting Another Cells Font Based On Different Cells Text

May 3, 2013

What I am looking to do is;

If cell A2 = "Closed"

then I want cells B2 and C2 to strike through its own text.

I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah

but if

A2 = Closed
then B2 and C2 = Blah blah blah

## Cell To Meet Same Colour As Another / Conditional Formatting

May 20, 2014

I need a formula which will, alike conditional formatting, change the colour of a cell based on date or another cell.

For Example, If I was to enter a value of today's date it would be green up to a 3 month period, after 3 months it turns to amber and after 6 months red.

I can do it with conditional formatting on the date function when converted to days (see attached). Is there any way I can make the date updated column automatically match the date difference colour with a formula?

Prices and Costs - Carbon Steel.xlsx