# Conditional Formatting For Highlighting Cells With Dropdown Values

Mar 19, 2014I want to highlight all the cells in a worksheet that have dropdown lists in blue color. Not sure how to do it.

View 13 RepliesI want to highlight all the cells in a worksheet that have dropdown lists in blue color. Not sure how to do it.

View 13 RepliesADVERTISEMENT

Is it possible to highlight a1:h1 if cell a2 contains dark?

i used this code in cell i1-->=MATCH("dark",a2,0)

and i dont know what's next, my friend in the office told me to use conditional formatting.

I'm having issues with conditional formatting. I used the INDIRECT formula to highlight specific rows.

For example, column A has the days of the week. I wanted the whole row highlighted if it was a Saturday. I used:

=INDIRECT("a"&Row())="Sat"

What I want to do now is to highlight specific cells (Columns F and G) if column A has Friday in it. I do not want the whole row highlighted if it contains Friday.

I am building a spreadsheet with a column having two drop down menus at the top that designate independent, but strict conditions. For simplicity, say I have in cell A1 a drop down menu with choices "alpha, beta, gamma" while A2 has a drop down menu for "1, 2, 3". These six values sit off to the side in cells X1, X2, X3, X4, X5 and X6 respectively.

In cell A3 I woul like to change its color, perhaps to green because it is an acceptable number (say less than or equal to 100) or change to red if unacceptable, value greater than 100.

I can do both of these commands in Excel, no problem. In my situation, I would like to change the value at which cell A3 goes from green to red dependent on the options in A1 & A2. There are 9 such combinations of alpha, beta, gamma, paired with 1,2,3 and I would like the combinations to change the fill color of cell A3 in the following manner:

If A1 = alpha and A2 = 1, then A3 will be green for values less than or equal to 100 and red for values greater than 100.

If A1 = beta and A2 = 1, then A3 will be green for values less than or equal to 80 and red for values greater than 80.

If A1 = gamma and A2 = 1, then A3 will be green for values less than or equal to 60 and red for values greater than 60.

If A1 = alpha and A2 = 2, then A3 will be green for values less than or equal to 95 and red for values greater than 95.

If A1 = beta and A2 = 2, then A3 will be green for values less than or equal to 75 and red for values greater than 75.

If A1 = gamma and A2 = 2, then A3 will be green for values less than or equal to 55 and red for values greater than 55.

If A1 = alpha and A2 = 3, then A3 will be green for values less than or equal to 92 and red for values greater than 92.

If A1 = beta and A2 = 3, then A3 will be green for values less than or equal to 72 and red for values greater than 72.

If A1 = gamma and A2 = 3, then A3 will be green for values less than or equal to 52 and red for values greater than 52.

How to create a cell that can change its conditional formatting based on the selected values of two drop down menus.

I have a few other conditions I would like to implement, say change the fill color of A3 to green, orange or red dependant on the values in A1 & A2, but I might be able to crack that if I can see how the above scenario is done.

In one column I have a drop down list of a dozen different initials with conditional formatting on those cells. I would also like to conditionally format an adjacent text string column based on those initials, but don't want to set up a dozen rules on each cell, one by one. If I set up the top cell with the correct rules I cannot fill the formatting in because the formula is not updated on a row by row basis, but en bloc for the range.

View 3 Replies View RelatedI have made a price comparison table.

It is set out as follows:

B = price , C= supplier, code D = price, E = supplier, code F = Price, H = supplier code, J = Price, K, Supplier code, L = Price, M = supplier code, N = price, O = Supplier code.

Each row is a product, so I am trying to compare each suppliers price (B,D,F,H,J,L,N) per row and highlight the cheapest price.

Whenever I get it to work it looks for the cheapest price in the entire table rather than on a row by row basis.

how to highlight a particular cell red if the value in one cell is less than or equal to the value in another cell. For instance, How do I make it so that the "Hours to go" cell turns red if the value in the "Actual Last Completion" value is less than or equal to the value of "Last Completed" cell?

View 3 Replies View RelatedI have a query relating to conditional highlighting - specifically highlighting a cell's column and row based upon whether it contains data or not i.e. if it is blank, it's column and row remain unhighlighted, but when it contains data, it's column and row should become highlighted. I could probably manage this myself but I have a few more specific requirements which I have illustrated with a few screenshots, however I can't see how to make these available to view here!I

View 1 Replies View RelatedI am wanting to highlight the ID's on list 2 which don't appear on list 1 and vice versa highlight the ID's on list 1 which don't appear on list 2.

View 2 Replies View RelatedI am using this Formula =Sum(Offset(A1,,,D1)) where I put for example number 4 in D1 and I get the sum starting cell A1 till Cell A4

I want the range to be highlighted as well, but I don't know how to do it.

I want to have Excel highlight one or two numbers/cells in one column based on the value in another cell. I am attempting to do this using Conditional Formatting.

For example, if X1 is BETWEEN A2 and A3, I want to highlight A2 AND A3. I have used various AND and IF functions to achieve this goal.

Also, if X1 is EQUAL to A2, I want A2 to be highlighted. This is easy to do with a simple formula.

But my problem is this: if X1 is equal to A2, I want ONLY A2 highlighted. If X1 is between A2 and A3, I want BOTH A2 and A3 highlighted. I cannot figure out how to achieve both. For example, when X1 = A3, A2 AND A3 are being highlighted, instead of just A3. I would prefer to do this without a macro, if possible. Can this be done?

Example formulas I have tried which easily achieve the highlighting of two cells when the value is between them:

Condition 1=IF(AND($I$35>I8,$I$35<I7),1," ")+IF($I$35<>I7,1," ")

Condition 2=IF(AND($I$35<I6,$I$35>I8),1," ")+IF($I$35<>I7,1," ")

It appears that the +IF, etc. functions are not doing anything.

This is more complicated by the fact that the value of, in the above example, I35 is based on a value from another sheet (I used a function to transfer a value from one sheet to I35 in the other sheet). As data are entered on Sheet1, the value in I35 in Sheet2 changes.

Well I spent some time yesterday trying to figure this one out. It was only last week that I have taught myself how to use VBA in excel. However this looks like a though one.

Simply what i would like to do is highlight a column of a certain date (till a certain row number) based on a list of dates.

eg. Across the top I have a set of dates and on another sheet i have a list of dates.

I understand that there is a VBA code for such conditional formatting but i can't get my head around it.

I have 2 worksheets. One has locations with numbers, example:

275 Location 1

276 Location 2

I have all my locations on the second sheet, but in 2 different columns, listed with numbers only, example:

271 275

272 300

I have 2 scenarios I need help with.

1st:

If any of the numbers on sheet 1 match the numbers in column 1 on sheet 2, highlight the number on sheet 1 in green If any of the numbers on sheet 1 match the numbers in column 2 on sheet 2, highlight the number on sheet 1 in red

2nd:

If the number on sheet 2 matches any number on sheet 1, highlight green

I want these to apply to all the cells that have numbers (it could apply to all cells I guess as it should ignore it if it doesn't match, I would assume)

I have a data with say around 500 rows and want to determine MAX for each row and HIGHLIGHT them, also if data in all cells is zero then it should ignore and highlight none.

I have tried this formula

=B2=(MIN(IF($B2:$E2>0,$B2:$E2))).

But this highlights all the zeroes, you can refer attachment for sample..

I'm using Excel 2010 on my Mac. I would like to set up the data as follows:

A

B

C

D

E

F

NAME

DATA X

DATA Y

DATA Z

DATA XX

DATA YY

NAME

90%

88.2

410

88.4%

97.8

[Code] ........

Now what I would like to do is have the data in each column highlighted a certain color based on specific ranges. For example in Column B I want anything from 92% - 100% highlighted blue. I would like 83% - 91.9% highlighted purple, and anything under 83% highlighted red. Do I need to have a conditional format entered in each cell? Can I set up a conditional format for the whole column? Once I can figure the formatting in my example the remaining columns will be a breeze since they will be set up in the same manner just different number ranges, same colors will apply.

I have 3 values in C3, D3 & E3 which need to be all the same at all times but when they are not I'd like to highlight the one that isn't with a background colour.

View 9 Replies View RelatedI have two date columns, say E & F. In these two columns I have entered date values. I need to do colour, font bold, Italic automatically when date values are entered in these columns - with a conditional formatting - i.e. every month should be in different color.

For example, values 23/05/2009, 15/10/2005, 07/12/2004 must be automatically coloured with different colors as its months are different.

I enter data in Cells starting at C3:O13 I enter 4 digit numbers 1131, 1212, 1122, 1215, 1201, 1207, 1133 etc. etc.

Here is my rule: I cannot have more than 8 numbers in the 1200 series in ROW 3 So I need conditional formatting to come up with a yellow background if I have 8 numbers in the 1200 series.

Example:

C3, C4, C5 etc.etc. 1131, 1212, 1122, 1215, 1201, 1207, 1214, 1233, 1122, 1230, 1216, 1122

1216 would highlight because it was the 8th cell in that row that contains a number in the 1200 series.

Ok so my project is tracking how many days have passed since a collection notice has been sent. Assuming the case isn't closed, a response from the customer hasn't been received, the case has been assigned to an employee, and assuming the notice was actually sent in the first place.

There are instances where the collections case may have been closed without a response date or any other date and we call that "Administratively Closed."

This is my formula: =IF(OR(CaseStatus="Closed",ISBLANK(NoticeSentDate), ResponseDate""),"",TODAY()-NoticeSentDate)

Problem is, now that I'm trying to conditionally format the ones >60 days, and =45 days WITHOUT getting the "blank" cells to change color too.

I got the one for >60 days: =AND(OR(CaseStatus"Closed", CaseStatus"Unassigned"), Comments "Administratively Closed", DaysPassed>60, ResponseDate="", DateEnteredInDatabase"")

I can't get =45 without having a bunch of cells that appear blank change color too.

I have a matrix in excel to show % and $ for multiple columns - set up like this:

Rows Part 1 Part 2 Part 3

Criteria 1 % $ % $ % $

Criteria 2

Criteria 3

the $ columns are conditionally formatted to highlight the top 10 values on the matrix. is there a way to have the corresponding % cells also highlighted? The % and $ aren't both top 10, so the % would have to be highlighted based on the $ top 10 cells.

I need conditional formatting formula that evaluates the values in a row of cells (7 total columns) and counts the number of entries that are 30 or greater. If there are at least three, then I need to apply formatting.

I just can't get the formula right:

Code:

=(COUNTIF(B7:H7,

I'm running Windows XP with Excel 2010. I would like to be able to have a group of 19 boxes of which each box has a unique entry, ie 1, 3B, 5C etc. Each box I'd like to have a way in which when selected only the single or dual unique characters show as a color. See attached spreadsheet with sample. When a cell with lets say 1 is selected, a drop box appears and the color and description are shown. Select a color but only show the 1 and the color selected, leaving behind the description. How can I do this for all 19 boxes. This seems like its a classic conditional format. Unfortunately it appears to be several orders of difficulty than a normal conditional format.

View 2 Replies View RelatedI 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).

I need to highlight a cell when its value exceeds parameters based on the production line it comes from. If the line is K11, then i need this cell to highlight when its value is either < 0 or greater than 221. If the line is K21, than it needs to highlight when its value is <0 or greater than 474.

A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.

dust 1.xlsx

I found code online that I can put on my sheet to get my formatting properties to stay the same for the items in my dropdown list located on another page. However the code does not work for conditional formatted cells...which is what I need. This is the code that I have that will carry over regular formatted cells. Just not Conditional formatted cells.

View 1 Replies View RelatedIm using access 2003 and the conditional formatting is only limited to 3. But i wanted 5. I was told it can be done using VBA. But I've got no programming experience whatsover.

Basically this is what i want, but i dont know how to translate this VB

IF Cell Value = >1 then make the value "Bold", "Bordered", and "Fill" with Orange colour.

IF Cell Value = "H" then make the text "Bold", Bordered, and "Fill" with Green colour.

IF Cell Value = "S" then make the text "Bold", Bordered, and "Fill" with Yellow colour

IF Cell Value = "A" then make the text "Bold", Bordered, and "Fill" with Blue colour

IF Cell Value = "U" then make the text "Bold", Bordered, and "Fill" with Red Colour

I am having a very difficult time trying to figure out a conditional formatting formula for something that seems very simple. There are 3 conditions I would like to apply to cell B2: If the value of I2 is less than or equal to 24, but greater than or equal to 21, shade B2 red. If the value of I2 is less than or equal to 20, but greater than or equal to 12, shade B2 orange. If the value of I2 is less than or equal to 11, but greater than or equal to 8, shade B2 yellow. I am sure I am missing something quite simple here.

View 5 Replies View RelatedCan Conditional Formatting be set on multiple values? I cannot get this to work with any formula, the cell values are not between two values. For example: for value = D or R, or X change color to green. (I would have another condition within a range F to Q)

View 4 Replies View RelatedFor use in conditional formatting, how do you check three values are identical?

=exact doesn't work, x=y=z doesn't work.

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