# 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 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 - 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 (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:

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

## Conditional Formatting In Column

Mar 26, 2008

I've copied a sample of my worksheet below (there are 52 of these in my workbook). i'd like excel to place conditional formatting in column M (i.e. "Promo Proice Per Unit"). So that if the price is different to the price in column L (i.e. "Shelf Price Per Unit") then excel will apply conditional formatting to the corresponding cell in column M.

So for example, in row 10 (product 5) the promo price (column M) is different to the shelf price (column L). It'd be great if excel wold then automatically format that cell as the example in the below table (i did the formatting manually).

When i tried to do this using the format/conditional formatting way i couldnt figure out how to tell excel what to do as its a little more advanced than the options that it gives you in that window?

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH1=HIJKLMN1Week commencing - 04-Aug-08  ???   2 Weekly Baseline Sales Log BaseRetail Price  3Account A      4 Sales MTSales UnitsSales - Shelf Price per UnitPromo Price per Unit% Discount5       6Product1          789.5        3,158.0 6.671\$4.19\$4.190.07Product2       1,529.0        1,529.0 7.332\$4.19\$4.190.08Product3       1,373.0        5,492.0 7.225\$0.00\$0.000.09Product4              8.2             16.4 2.105\$0.00\$0.000.010Product5       1,636.5        6,546.0 7.400\$4.19\$3.490.011Product6       1,363.0        5,452.0 7.217\$4.29\$4.290.012Product7       2,829.0        5,658.0 7.948\$6.99..........................................

## Conditional Formatting For Column D

Jan 29, 2010

I want a to include conditional formatting for Column D in my spreadsheet to highlight cells that have information in them but that are less than 17 digits. (The cell will be filled with alpha & numeric data)

How do I do this?

## Conditional Formatting Of A Part Of Column

Dec 21, 2013

I'm trying to build a simple spreadsheet that allows me to auto highlight the day in the week and part of the column.

I include a sample sheet... above saturday there is the formula I use to determine the day of the week.

## Conditional Formatting - Full Row/Column

Oct 13, 2009

If a cell in Column F = Closed Then, the entire row is filled green. If a cell in Column F = Open Then, the entire row is red with a strike through. I'm guessing this is simple, but I can only conditionally format on a cell-by-cell basis right now

## Conditional Formatting Based On Value In Another Column

Feb 17, 2014

I have a value in column H7 as 2000.

I have columns M to X labeled Jan to Dec. I want to highlight in yellow values in the row M7 to X7 if they are greater than 2000!

Then, I need to copy this conditional formatting down the columns under M to X, but under column H the values will be different, but I still want the same results, values greater than the value under column H then highlight in yellow.

I thought I had the answer but when I tried to copy down, it was making cells yellow that were clearly less than the column H value.

## Conditional Formatting With Reference To The Whole Column

Jun 29, 2006

I have made a sample file with conditional formatting. The file contains 3 sheets which contain a number of orders. Conditional formatting is changing colour of the whole line depending on value in column C. The formatting contains reference to a certain cell in column C. It is very easy to make when the lists are so short: I make conditional formatting for line 2 with reference to cell C2, copy formats in the other 2 lines and change formula references to C3 and C4. However in the original spreadsheet there might be more than 5000 lines per sheet and it will be too time consuming to correct the formulas manually. Does anyone know how I can make a reference to column C without specifying each particular cell?

## Conditional Formatting With If Statement - Response Due Column

Mar 25, 2014

I created added conditional formatting to the Response Due column, to keep track of when response was due.

I only want the conditional formatting to be applied if the cell next to it is blank.

For example, the last one on my attachment is due 3/26/14 but I have already submitted a response (see column K)

What can I add so that only those dates next to a blank in column K get the conditional formatting?

Sample.jpg

## Conditional Formatting (column H Changes To Yes I Want The Background Of The Whole Row To Change To Red)

Jan 6, 2010

I have a spreadsheet where column H can be either yes or no. When column H changes to Yes I want the background of the whole row to change to Red.

e.g. Cell H4 contains the word no. I then change this so that it says "yes". I want the background of row 4 to go red. so A4, b4, c4, d4, e4, h4 all go red.

With the type of conditional formatting i normally do i can only get cell H4 to go Red by putting cell value = yes as the condition. I cant for the life of me think of a formula or way round of doing this.

## Conditional Formatting Based On Status Column?

Sep 29, 2011

I am putting together a worksheet with all my teams tasks. I have columns A:U and Status is column "M". What i want to do is when ever i select Complete from the list in Column "M" i want to the row to turn Green.

I tried the following:

Selected the Range
From Conditional Formatting menu selected Manage rules
Selected new rule then Use formula to determine which cells to format
formula i used =\$M\$2=complete and selected green from the format menu

BUT nothing is happening. What am I doing wrong.

BTW the Values in Column are Complete,Development, Not Started Research, QA/UAT. If I can conditionally format based on these selections that would be even better.

## Conditional Formatting To A Column Based On Date

Mar 7, 2012

I have a column of cells containing dates. I need to change the colour of any cell which is 7 days older than todays date. I would like to leave the header and any blank cells unchanged. Every formula I've tried has changed the enire column, or if I selected a range, the entire range.

## VBA Macros For Conditional Formatting And Column Hiding

Oct 11, 2013

I am totally new to Macros. I need a Macro which should format a column based on the value of another column.

Consider I have 10 rows. I have to format column D, based on the value of Column E. If the value of Column E is > 1000, then the background color of Column D should be changed as green. The most important requirement is Column E should be invisible, Changing the font color of Column E as White does not seem ok cuz when we select the sheet entirely using Ctrl+A, the white values are very much visible. Can this be achieved using a macro?

## Conditional Formatting Based On Adjacent Column Value

Feb 25, 2009

I'm trying to highlight cells a certain color based on the value of the cell 1 column to the left. How would I be able to do this in conditional formatting?

Example)
Highlight the cells in "Visit 2" column if it is greater than the "Projected Visit 2" column.

NameProjected Visit 2Visit 2Person 124-Nov-0820-Nov-08Person 226-Jan-0927-Jan-09Person 32-Jan-082-Jan-08Person 430-Dec-0829-Dec-08Person 46-Nov-0830-Oct-08

## Conditional Highlighting Of Rows?

Jun 24, 2014

I have a program that compiles a whole bunch of annual data.

The data is in columns A:J

I would like to have a program that loops through column A's entries and if the entries are odd then have the cells in Columns A through J of that row be highlighted with colour index RGB (193, 205, 205).

This is what I have below, but it is not working.

[Code] .....

## Script To Add Conditional Formatting Rules To Column Of Cells

Dec 2, 2013

I'm having to successfully add three conditional formatting rules, and associated cell interior colour formats if any of the rules are met.

Each time I run my macro, I want to refresh every cell, within a defined range within a Col P, with these rules.

So far, I can add the three rules to each cell, but when I try and add the format colour - for when a rule is met - I keep getting a 'Subscript out of range error'.

(As an aside, is my For / Next loop approach the best one to take to add the formatting to each cell, or can it be written much simpler?)

Here's my script:

VB:
Dim Col_P As Range
Dim Cell_in_ColP As Long
'Set up range of cells to add conditional formatting rules to
Set Col_P = Worksheets("tRIIO Pack Notices").Cells(2, 16).Resize(Lrow - 1)

[Code] ......

## Conditional Formatting - Use Info From 2 Columns To Format 3rd Column

May 24, 2014

conditional formatting in Excel. I have two columns with pertinent information. I need to know the following and format accordingly:

1. Is the number in column A positive or negative?
2. Is the number in column B less than 0.05 or 0.10?

I would then like Column C to just be highlighted a certain color depending on the combination... there are 5 possible combinations and I would like the cells to be formatted so that:

1. Positive and less than 0.05 - Bright yellow
2. Positive and less than 0.10 - Pale yellow
3. Negative and less than 0.05 - Bright green
4. Negative and less than 0.10 - Pale green
5. This "combo" just means the criteria wasn't meant... which is possible b/c sometimes Column A may have text instead of a number of b/c the number in column B is not less than 0.10. If either of these is true, I want the cell to remain blank.