Conditional Formatting - Highlighting One Or Two Numbers In A Column?

May 18, 2013

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.

Conditional Formatting - Column And Row Highlighting

Nov 5, 2013

I 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

Conditional Formatting - Highlighting Lowest Value Per Row

Apr 6, 2014

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

Conditional Formatting - Highlighting The Value In Particular Cell Red?

May 29, 2014

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?

Highlighting The Cells Using Conditional Formatting

Aug 19, 2009

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.

Conditional Formatting - Highlighting New Additions To List

Jan 14, 2013

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

Conditional Formatting / Offset - Highlighting Range?

Feb 2, 2013

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

Conditional Formatting - Highlighting Specific Cells

Jun 7, 2013

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.

Conditional Formatting Of Dates - Highlighting Of Columns

Jul 11, 2006

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.

Conditional Formatting Highlighting Data That Matches On 2 Different Worksheets?

Jul 17, 2012

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)

Conditional Formatting For Highlighting Cells With Dropdown Values

Mar 19, 2014

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

Conditional Formatting In Excel For Each Row Showing Max / Highlighting It And Ignoring Zero

Jun 1, 2014

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

Excel 2010 :: Highlighting Data Based On Conditional Formatting

Apr 1, 2014

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.

Conditional Formatting Text To Numbers?

Jun 26, 2013

I'm designing a spreadsheet for our department which tracks a students progress throughout their time at the school. There are 4 levelsStatementSchool Action PlusSchool ActionMonitorIn year 7, they may come into our school at Monitor level, but when they get to year 8 they may have moved to Action Plus. I want to highlight that change on my spreadsheet with conditional formatting if possible so if they go up, the cell will turn red, if they move down the list the cell will turn green.

I've created two columns on an options sheet (which holds all my data validation lists) and assigned a number to each of the levels so i can conditionally format my columns using < and > but I don't know how to write the lookup formula

What I'm trying to achieve
ExcelHelp.jpg

My Options sheet
Excelhelp2.jpg

Conditional Formatting With Letters And Numbers ...

Jan 27, 2009

I found out about conditional formatting for five different letters or letter combinations, using VBA, using the code below.

I was wondering if this is also possible with letters or letter combinations, BV, RV, SV, CV, Z and numbers, somewhere between 0 and 9,5.

Round Odd Numbers For Conditional Formatting

May 5, 2009

I have a sheet that has conditional formatting that looks at column D2 for a number and divides that by half. it then colors in a corresponding cell on a row in the sheet that matches that number. the problem i am having is with odd numbers like 23 it gives a result of 11.5 . all my cell numbers are all rounded to whole numbers. i need to round the 11.5 to 12 or 13 it does not matter which one... i just need it to round it up or down. I have tried everything i can think of but have had no luck. I attached my workbook so you can see how it works.

Non Matching Numbers In Conditional Formatting

Apr 23, 2014

I have a number in column "E" T13245v1

I have a formula in column "D" =RIGHT(E1459, 1)

Which gives me "1" in column "D" (last number in string)

In column "P" I have a number which I want to apply conditional formatting to, to highlight if the numbers in "D" and "P" do not match.

What is happening is that the conditional format is seeing the formula not the number and therefore always applies the condition. How do I correct this.

Conditional Formatting: Color The Numbers

Feb 14, 2007

suppose I have the numbers 1-25 in a column. I want to color the numbers 1,4,7,10,13,16,19,22,25 green, color 2,5,8,11,...25 orange and color 3,6,9,12...25 red. I can not set up the conditional formatting formulas correctly.

Conditional Formatting Based On Numbers And Texts?

Jun 27, 2014

I have column A with Different status like Open, closed, available etc. Column B have numbers. i am looking for a formula in condition formatting for column B.

If the cell value in column B is 2 or more, the same cell should be highlighted in Green.

If the cell value in column B is 1, the same cell should be highlighted in Yellow.

If the cell value in column B is 0, the same cell should be highlighted in Amber.

If the cell value in column B is less than 0, the same cell should be highlighted in Red.

If the cell value in column A is "Available", cell in column B should be highlighted in Amber.

Applying Conditional Formatting To Numbers In Brackets

Jan 28, 2014

I have some data referring to % that comes in the format of 1.1% for a positive number and (1.1)% for a negative number

How can i get the 1.1% to display green and the (1.1)% to display red?

How To Make Conditional Formatting For Negative Numbers

Feb 19, 2014

I need the conditional formatting to make all numbers that are zero clear (i.e. no fill).

I need it to make all negative numbers to be red, however it doesn't seem to recognize "-1" as a number, and ends up highlight everything red when I say "highlight values < -1 red".

How would I do this?

Conditional Formatting Color 3 Numbers In Another List?

Mar 6, 2013

formula I colored only if three numbers in a list.

attach file

formattazione.xlsx

Conditional Formatting Formula For Decrease In Numbers

Oct 16, 2013

I have a table that has labels of departments. Then each of those departments will be tracked per week on a number. I am looking for a formula for conditional formatting that if cell C5 is less than B5 that cell C5 (with the smaller number) fills green. Then depending on the next weeks number it will have no fill or a green fill if the number has decreased in cell D5.

Is there anyway that I can do an array of cells or do I have to do it individually per column?

Conditional Formatting For Lots Of Different Item Numbers?

Jul 13, 2014

What would be the best way to apply conditional formatting where you actually want to apply a background and foreground colour to cells that have many different item numbers, ex:

6201 = Dark blue font, light blue background
6202 = Dark Green font, light green background
6230 = Dark Purple font, light purple background
6245 = ...
6445 = ...
6447 = ...
6448 = ...

and the list goes on for about 200 different colour combinations.

I know I can do this in conditional formatting using the ribbon, but I wonder if there was a faster way to do this

Conditional Formatting - Calculate The Numbers Of Month And The Revenue?

May 5, 2014

i wish to do some conditional formatting. my target for the first 6 months from date activated is 50,000 after 6 months is 100,000. and for those below target, i will need it to be highlighted in red. thus i have 2 sets of conditions.(calculate the no. of months and the revenue).

Conditional Formatting - Highlight Numbers In Data Group

Mar 25, 2013

How do I do "conditional formatting" to high light the numbers which bigger than average + 2 * stdev in a data group?

Unable To Apply Conditional Formatting With Numbers ( Font And Fill Of Same Color)

May 22, 2014

I m unable to apply conditional formatting with numbers ( font and fill of same color).

like:-

if press 1 , cell and font should be of same color and if 2 with different color and so on

as of now either formula is applying on cell or font but not on both

Conditional Formatting (largest Number In Column B The Hotel In Column A Should Be In Bold)

Oct 18, 2008

when the largest number in column B the hotel in column A should be in bold.

So in excel language IF(Number in B Is Max display corresponding hotel in column A as BOLD. But I can't figure out how to do this.

You can see here on the image:

Excel 2007 :: Conditional Formatting Of Numbers Will Not Stay Cleared On Reopening File

Apr 27, 2013

Excel 2007 Conditional Formatting. I am trying to apply conditional formatting to a wide range of cells. I only want the rule to adjust the color and shading, under the FONT tab in the section below. Specifically, I do NOT want to adjust the number format (I have the user select the scaling and use VB to populate the number formatting). I accomplish this by removing the number formatting by going into the conditional formatting rule, clicking on format, and then on the first tab (Number) -- on the bottom right there is button to Clear.

This resolves the issue perfectly, and I can operate the report exactly as I would expect.

Unfortunately, when closing the report (after a save), and then re-opening, the conditional formatting number format is no longer cleared. Worse yet, because it no longer is cleared, the scaling VB doesn't work, unless I go back into conditional formatting rules, and go to the number tab, and click clear.

Screen shot of after re-opening, no changes:

Report looks terrible (not real data):

Conditional Formatting Each Column In Itself

Feb 1, 2014

I have a column containing 8760 item (365*24), I divided them into days, one column for each day, modifying a macro I found here. Now I want to conditionally format these columns in themselves with color scales. But if I select 365 columns at the same time and do it, it takes the highest and lowest of all the numbers and format. I want it to format each column using the highest and lowest values in itself. How do I do this without selecting every column one by one and turning conditional formatting on manually?