Free Microsoft Excel 2013 Quick Reference

Conditional formatting based on an external cell's value

Dear All:

I wish to do conditional formatting based on an external cell's value.

i.e. A1 should be formatted if the content in B6 is "example".

One of my friends suggested to use ISTEXT but is checking only if there is any text and not the specific text.

Please help.

Regards,
Gowri Shankar.


Hi there,

I would like to have a conditional formatting formula which can highlight a value and the top and left labels that correspond to it.

Here is my example :

Drop-down lists using Validation
Which Item ? __Bread___ (in cell M10)
Which Month ? __Feb_____ (in cell M11)

Result (using a combination of Match and Index)
Amount = __75_____ (in cell M13)

Jan Feb Mar Apr May Jun
Eggs 75 31 83 59 38 66
Bread 15 66 95 25 63 97
Milk 36 58 18 31 58 46
Cheese 25 75 62 38 95 28
Meat 14 45 44 49 45 54
Veggies 87 96 81 65 25 53
Kleenex 94 32 37 88 38 95
Shampoo 19 52 64 61 76 85

Conditional Formattings applied to entire table above
1) =NOT(ISERR(SEARCH($M$10,C10)))…..(highlights column title “Feb” correctly)
2) =NOT(ISERR(SEARCH($M$11,C10))) …..(highlights row title “Bread” correctly)
3) =NOT(ISERR(SEARCH($M$13,C10))) …..(highlights all 3 amounts of “75” incorrectly – need it to only highlight the 75 that corresponds to the intersection of item “Bread” and month “Feb”)

What formula (or combination of formulas can I use to achieve this ?

In short, I require conditional formatting to highlight an amount and it’s corresponding top label and side label based on selections made using a drop-down list.

Thanks in advance.

I need to create a conditional formatting based on whether a cell contains a letter value. (actually it is a checkmark Windings 2 font)

For example if C2 contains the checkmark font C4 through C43 will be have a Pattern filled in.

Attached is a working sample of what I am doing.

thanks I appreciate any help creating a formula to use in Conditional Formatting to do this.

Nick

I want to conditionally format a cell if a different cell has contains
certain data. I only know how to do this based on the selected cells
contents. Can anyone help?

Hello all - I need help using a conditional format based on a drop-down cell value.
I have a spreadsheet for scheduling - in this example I am using 2 columns, A1 shows a name, B1 has a drop-down list to select an 'absent code' (eg. 'V" for vacation). How can I use conditional formatting to change the color of A1 based on the value of the drop-down in B1? Any ideas would be very helpful....thanks

Hi Guys,

I have been trying without a success to apply a conditional formatting on the cells in each column to colour them in if the date in one of the top cells are the same as the one in B1 [=today()]

Each column indicates the date of the beginning of each week. So I was trying to come up with the formula that will get this formatting work [if $B$1 is equal or bigger than column$3 and equal or smaller than sum(column$3+6)]

The thing that is causing me trouble is that I really don't know how to implement that formula in conditional formating.

I have attached a sample file to show you what it looks like. I was trying on cell E5 to start off but couldn't get it to work.

If any of you guys have any idea how to implement a formula in conditional formatting as it is not based on the cell value it would be great.

Thanks for reading it and giving a try.

Best Regards
Simon

Hi,
I want to highlight all cells in a column based on their previous cell value.
For example....
In the column H, if h2<h1 or h3<h2 or h4<h3.....so on...they must be highlighted with red color indicating the lesser cell value than the previous one.

I can format only one cell based on above condition but unable to do this with all the cells in that column with the above condition.

Please help.

Thanks

Good Afternoon!

I've hit a bit of a roadblock with this spreadsheet I'm working on. I'm trying to create a visual schedule matrix for my office which gives a visual representation of who's on and who's off (whether due to break, lunch, or not on shift) to hand out to the supervisors and managers in my call center. I've gotten the basic matrix worked out (with some extensive formulas) with simple coloration. The issue I'm running into is differentiating different types of reps by what their job is. Most reps we have are phone reps, however we also have an email address that people write in to with questions. I'd like to separate the email reps by showing them as a different color. Here's a quick synoposis of what it looks like:

Rep Name | Rep Type (Phone or Email) | Individual 15-minute blocks which, due to an embedded formula, displays either ON or OFF when it checks what time-block it falls under vs the employee's shift. (Such as, if between Shift Start Time and Break 1 Start Time, it'll display ON, else it'll display OFF.)

I want it to show that anyone that's listed as Phone has their ON color as yellow, and anyone that's listed as Email has their ON color as a light purple (Colors are arbitrary, I know!)

Anyway this is what I thought would do it. I band-selected the entire Time portion of the matrix, sans headers, and added a conditional format based on a formula. This is the formula I used:

=AND($D$4:$D$23="Email", $E$4:$BA$23="On")

Where D4:D23 are the role designations for the reps (Phone or Email) and E4:BA23 is the visual matrix (With each cell containing ON or OFF based on the rep's schedule.) The Affected Area for this conditional format is the same, E4:BA23.

Nothing happened when I tried that. I understand that it's likely because it's coming up with a FALSE due to how it calculates it. I was expecting it to calculate the formula in each individual square, checking to see if, say in cell R12, if it contained ON, and then cell D12 contained Email, it would return TRUE, and therefore color the cell as I had planned it. This calculation would repeat across every cell in the affected area... well it did not do that or I wouldn't be posting here.

Anyway, what could I do to fix this? I would rather not have to resort to just doing a new basic conditional format for the affected rows, because I want to turn this into an automated tool that the managers and supervisors can use without having to delve deep into this spreadsheet's gizzards. I've attached a screenshot of this matrix to help explain the above a little better than what I can do with simple text.

IMPORTANT EDIT: Dummy Worksheet attached on my reply below, please use that instead of the JPG attached to this original post.

Hello, I was hoping someone could help me with a conditional formatting question in Excel 2007.

A little background: once a week we use some third party software to pull data from our database and create an inventory report in Excel. This report has multiple lines for each item in inventory and followed by totals on every 15th row. This sheet will generally be about 10,000 rows long.

I tried to color code this report to make it easier on myself to quickly scan through by highlighting the relevant columns and conditional formatting based on numerical ranges (i.e. red if less than or equal to zero, yellow from 0 to 10, red if greater). The problem is that this highlights a lot more than what i need and makes it even more difficult to scan through quickly.

So what i ended up doing was to highlight every 15th row using a formula in conditional formatting [=AND(MOD((ROW()-1),15)=0) --ROW()-1 because the first row is headings]. This highlights just the totals on every 15th row, but the problem is, now i can't figure out how to get the value of each cell to format based on numerical ranges.

Is something like this even possible?

Thanks in advance for all of your help.

Summary: I want to highlight every 15th row of data red if <= 0, yellow if between 0 and 10, and green if >= 10.

I'm using excel 2003 and wanted to know if it is possible to do conditional formatting based on the reference values of other cells. In the attached document, can I make columns Z, AA and AC shaded to the same color as AB?

My goal is to select this entire week of cells and just drag it down for a year.

I am trying to format cells based on what is in them across the board.

Is there a way to conditional format based on what is in certain cells?
I have 5 columns. I need to color in every NA only in the rows that have 1 or less cells with a number in there. So if there is 2 cells in that one row that have a number in them then leave the whole row white. If there is only 1 number in that row and the rest are NA then color all the NA's in red.

1 3 NA 5 NA ------- Leave White
NA NA 7 NA NA ------- Color NA's Red

Thanks for all the help you can provide.

Hi everyone,

I am trying to automate some tasks for reports that I have made. What I need to do is set conditional formatting based on a value and then later set a formula in a different cell. So far I have got the entire macro to work and apply the formatting plus enter the formula, however on the conditional formatting quotes are being added and I don't want them to because it makes the formatting not work.

For example my macro puts in that if a Cell Value > "$E$21" then do some formatting, but i want it to say Cell Value > $E$21.

This is my code:

	VB:
	
 Conditional_Format() 
     '
     ' Conditional_Format Macro
     '
     
    Dim j 
     '
    For j = 1 To 100 
        If Range("F" & CStr(j)).Value = "" Then 
             
        End If 
         
        If Range("F" & CStr(j)).Value = "max" Then 
             ' Selection.FormatConditions(1).StopIfTrue = True
            Range("G" & CStr(j) & ":K" & CStr(j)).Select 
            Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ 
            Formula1:="$E" & "$" & CStr(j) 
            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
            With Selection.FormatConditions(1).Font 
                .Bold = True 
                .Italic = False 
                .Color = -16776961 
                .TintAndShade = 0 
            End With 
            Selection.FormatConditions(1).StopIfTrue = True 
            Range("M" & CStr(j)).Select 
            ActiveCell.FormulaR1C1 = "=IF(RC[-1]RC[-8],""X"",IF(RC[-5]>RC[-8],""X"",IF(RC[-4]>RC[-8],""X"",""v""))))" 
        End If 
         
        If Range("F" & CStr(j)).Value = "min" Then 
             ' Selection.FormatConditions(1).StopIfTrue = True
            Range("G" & CStr(j) & ":K" & CStr(j)).Select 
            Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ 
            Formula1:="$E" & "$" & CStr(j) 
            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
            With Selection.FormatConditions(1).Font 
                .Bold = True 
                .Italic = False 
                .Color = -16776961 
                .TintAndShade = 0 
            End With 
            Selection.FormatConditions(1).StopIfTrue = True 
            Range("M" & CStr(j)).Select 
            ActiveCell.FormulaR1C1 = "=IF(RC[-1]RC[-8],""X"",IF(RC[-4]>RC[-8],""X"",""v""))))" 
         
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I believe the line that controls this is

	VB:
	
(j) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated, thanks in advance.

Is it possible to implement conditional formatting based on text contained
in a cell? Example, user wants to be able to highlight all the cells in a
spreadsheet for a given entity (which will change all the time) so I'm
looking for something like "cell contains xyz" -- but as far as I can tell,
it works only with numeric values.

Am I trying to do something that is impossible?

Thanks in advance.

I am trying to create a conditional formatting based on another cell having a positive number.

If cell C16 is a positive number, then cells A16 & B16 will fill/shade with a color.

I am not sure if this needs to be done with a formula or if there is another way.

Thanks for any help.

Nick

Is it possible to use conditional formatting based on the contents of another cell. eg. I have a table of information, and in column A I type "Y" to indicate that the row has valid information. Once I type "Y" into say A1, I want to make the complete row A to change background colour.

Suggestions?

Scott

Hello,

I am posting today to find out if there is anyone here that may know how to create conditional formatting based on the TODAY() function. basically I want along ROW1 of my excel spreadsheet a list of dates, so for example A1 to A10 will be shown like this 01/01/2012, 02/01/2012, 03/01/2012 etc... Then on ROW2 within the same range it will have the formula =TEXT(cell above,"DDD").

Now using conditional formatting when the date in ROW1 equals present day it will change the background colour of the cell to a different colour and in addition also change the colour of the cell below to the same colour. I've been trying this for a bit now and I can't get my head around it.

I have also created an excel workbook that I have attached to this post.

I am trying to create an conditional formatting, not sure if it's possible without getting into macro or vb programming.

Excel Sheet Description:

I have a setup of items that students can check out.
Name.................Date Checked Out...............Date Checked In.............Item
Student 1............01/01/2010..............................................................Dictionary

Problem
I would like to shade each row the following colors depending on the following criteria:
if the item has been checked out more than 30 days: green, 60 days: yellow, and 90 days:red. If it's less than 30 days, no shading.

I created an if state, where if the check in date is 0, then take today's date minus checked out date, otherwise take the checked in date minus the check out date (negative value). I get the results, but have no idea how to apply it and make the whole table format base on an if statement.

Any ideas will be greatly appreciated.

Hi,

Is it possible to generate a conditional format based on a validation rule?

What I want to do is:

I added a number of validation rules to my table and I want to find the invalid cells so I can fix them. (I inherited the table, otherwise I would have the rules before data enrty).

I can use the "circle invalid data" function but 1) it takes a long time and 2) when I edit a cell the circles disappear and I need to wait all this time again for them to reappear when clicking the "circle invalid data".

Any ideas?

If I could apply a conditional formatting such that invalid cells will be formatted red it would be awesome, but maybe there are other solutions out there.

Cheers,

Yoav

I need to create a conditional format based on a text string that will be
contained in a larger text string within the active cell. I know I can use
either the SEARCH or FIND function to determine if a text string is contained
within a larger string, but those functions require a cell address and I
don't know how to apply them to the current cell address, whatever it might
be.

So, for example, if the text string is "needs training", and the text in
cell A5 is "George has had some exposure to the app, but he needs training",
then I would want that cell to apply a conditional format because the phrase
"needs training" is contained in the cell.

Thanks in advance for any advice on this...

I need to create a conditional format based on a text string that will be
contained in a larger text string within the active cell. I know I can use
either the SEARCH or FIND function to determine if a text string is contained
within a larger string, but those functions require a cell address and I
don't know how to apply them to the current cell address, whatever it might
be.

So, for example, if the text string is "needs training", and the text in
cell A5 is "George has had some exposure to the app, but he needs training",
then I would want that cell to apply a conditional format because the phrase
"needs training" is contained in the cell.

Thanks in advance for any advice on this...

I have a spreadsheet with cells of various currency formats within the same columns & rows. To make it highly visible, is it possible to write a formula to apply conditional formatting based on currency format (without using VBA)?

If the cell is formatted to USD, make it green
If the cell is formatted to Euros, make it blue

Hi there

I'm attempting to create a worksheet that will plot a coloured cell on a chart (conditional formatting) based on a 1-10 score of two seperate questions.

Question 1 will plot on the 'x' of the chart based on 1-10, Question 2 will plot on the 'y' also 1-10.

I'm hoping this will allow me to plot on a matrix based on the scoring of two combined questions, so I can assess risk etc.

Can this be done?

Any assistance greatly appreciated!

I have a spread sheet in which I need to set up at least two conditional formats based on date. It is a list of contracts with expiry date in one column, followed by 52 other columns, 1 for each week of the year. Say for example a contract expires on 31/01/2011, I would want all cells in the row from week 4 onwards to automatically turn red, as I cannot use this contract in those weeks. The cell that contains the date itself will automatically turn red using a NOW formula, however after a lot of messing about and time wasting, I just can;t get the 52 remaining columns to link to the expired date. Can anyone advise? Example attached.

I have two worksheets with location names on them. Each location name is unique. The lists are generated for two different weeks. I would like to set conditional formatting so that if a Location on the 2nd sheet shows upon the 1st sheet, it is highlighted. (So I can flag which locations showed up twice in a row.)

Is this possible? To do a conditional formatting based on a list? That is, "if this location on Sheet2 is equal to a location on Sheet1, then highlight the location on Sheet2"

Thanks!

Calinda

Can anybody help me to fix this macro i have it to where is highlighting a single cell i will like the macro to highlight entire row base on a single cell value

thankyou for any help provided
Sub colorentirerow()
'base on a value of single cell highlight entire row
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
For Each mycell In Selection
If mycell.Value Like "5" Then
mycell.Interior.ColorIndex = 3
End If
Next
End Sub