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.

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.

- Conditional Formatting based on 3 different cell values (criteria)
- Conditional formatting based on a cell value
- How to do a conditional formatting based on an adjacent cell
- Help with conditional format based on a drop-down cell value or formula?
- Conditional formatting based on formula not cell value
- How to apply conditional formatting to cells based on their previous cell value.
- Conditional Formatting based on Multiple Cells
- Conditional formatting based on multiple criteria
- Excel 2003 - How do to conditional formatting based on cell references?
- Conditional Formatting Based On Cell Content
- Apply conditional formatting based on formula using VBA
- Conditional Formatting based on text?
- Conditional Formatting based on a positive number in a cell
- Conditional Formatting based on another cell
- Conditional Formatting Based on Today Function
- Conditional Formatting based on if funciton
- Conditional format based on a validation rule
- Conditional Formatting based on Text within Text
- Conditional Formatting based on Text within Text
- Conditional formatting based on currency
- Conditional Formatting based on two dependant Data Validation lists
- Automatically conditionally format based on date
- Conditional Formatting Based on Match to Separate List
- Highlight entire row base on a single cell value

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.

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

certain data. I only know how to do this based on the selected cells

contents. Can anyone help?

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

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

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

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.

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.

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

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.

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:I believe the line that controls this isConditional_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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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.

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

Suggestions?

Scott

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.

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.

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

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

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

If the cell is formatted to USD, make it green

If the cell is formatted to Euros, make it blue

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!

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

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

No luck finding an answer? You could always try Google.