Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Use Conditional Formatting To Turn Cell Red If Any Cells Above Are Blank

I have a formula in A56 that adds cells A1:A54. I want to use conditional formatting to highlight A56 if any of the cells in A1:A54 are blank. I am unsure how to do this.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Conditional Formatting Formula Used To Turn A Block Of Cells Green
I have attached a small file with a very simple conditional formatting formula used to turn a block of cells green.

However, the formula does not seem to work properly as it only turns half of the cells green. Columns B1 to B18 are also supposed to turn green, but instead remain clear.

View Replies!   View Related
Turn Off Conditional Formatting
I have a color change alert in a cell using Conditional Formatting. Is there a formula that will turn off the alert in that cell once a value, (any value), is entered into the cell?

View Replies!   View Related
Ignore Blank Cells In Conditional Formatting?
I have tried to set up a list of cells to highlight in red any numerical values which are greater than 0 using conditional formatting. This works fine, except that all blank cells are also highlighted in red. Formula is currently: cell value is greater than 0. What do I need to do to ignore the empty cells?

View Replies!   View Related
Turn #DIV//0!” To “0” Or Even Turn It To A Blank Cell
I have a workbook which is set up to take an average heart rate of a participant from a series of data points. I have set the spreadsheet up before I have collected some of the data. (so I can review the project at the 3 months period and its an ongoing project).

The problem is that if there is no data in a participants column then excel correctly gives you readout of “#DIV//0!”. On my results page this #DIV//0!” makes it hard to read the spreadsheet. Is it possible to get excel to turn #DIV//0!” to “0” or even turn it to a blank cell?

View Replies!   View Related
Conditional Formatting (start And End Times Of The Shift To Turn Black Or Red)
I want the start and end times of the shift to turn black or red depending on whether the adjacent cell says "off" of "Hol" respectively. I have this working except for when I actually enter smething into these cell ie a shift, the cell turns black.

View Replies!   View Related
Conditional Formatting If Cell Is Blank
I have a totals spreadsheet that links many other spreadsheets. I need to set up a formula for conditional formatting if the cell is blank (NOT ZERO). I need to ensure that the users are inputting zeros and not leaving the cells blank. I tried this but it's not working: ="IF('[Brown Deer-Q2-08.xls]Week 7'!G13)<"""

View Replies!   View Related
Ignoring Blank Cell With Conditional Formatting
I've read dozens of threads on how to have conditional formatting ignore blank cells but I am not understanding how to make it happen myself...

Logic: Highlight the cells that are >=10 but ignore the blank cells that have functions in them

View Replies!   View Related
Apply Conditional Formatting To A Cell When It Is Left BLANK
I'm trying to apply conditional formatting (shading) to cells that are left blank.

(Purpose: I am designing a research template for a client to complete with data and want the spreadsheet to show them where they've "missed a bit"!)

(When I go to the conditional formatting box, it asks me to specify when "cell value is"..."between/not between/equal to/not equal to" etc. But there's no option to specify when the cell is blank.)

View Replies!   View Related
Conditional Formatting - Blank Cell Gets Unwanted Format.
As a pert of a spreadsheet I have 2 columns (M5:N400) of 400 cells deep. Each cell in these columns contains a formula that counts days, when appropriate. They can end up showing a value of blank, a number between 0 and 5, or any number greater than 5. (But very rarely greater than 30)

If the number is greater than 5 then it means that it has exceeded it's target time and I wish to highlight this with conditional formatting (CF). I have a CF on all cells in both columns that says.

If "The cell value" is "Greater than" "5"... then
format the background colour to a pale yellow.

It works, but doesn't work.

If the cell value is 0 to 5 it's unchanged. Good.
If the cell value is >5 it changes. Good.
BUT, if the cell is blank it also changes. Bad!!!

I assume it is because it is looking at the fact that there is a formula in the cell, even though the displayed value is "". So I tried a second level saying that if the cell is equal to "" then no format, but it still changes.

View Replies!   View Related
Conditional Properties: If A Number In A Different Cell Is Above A Certain Number It Will Turn Green And Another Cell To Turn Red
can I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.

View Replies!   View Related
Conditional Formatting Certain Cells When Another Cell Is Highlighted
I want to change (via conditional formatting) the background colour of cell H64 and K72 when I select / highlighted / activate (i.e. just left click in the cell) cell C66.

I don't seem to be able to find in the formulae something to indicate if C66 is selected / highlighted /activated.

View Replies!   View Related
Conditonal Formatting (cell Automattically Turn Red Once The Date =NOW() Is The 15th Sept)
I have worked out the conditional formatting but i have a question. The cell has a date which is input manually - no formulas or anything
it reads 14/09/09. however can i have the cell automattically turn red once the date =NOW() is the 15th sept??

View Replies!   View Related
Conditional Formatting Date Cells Based On Entry In Adjacent Cell
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.

I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()

All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.

In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.

View Replies!   View Related
Changing Cells Colors In Relation To Cell Input - NOT Conditional Formatting Ques
I'm a average excel user, i know my way around it and kow common and basic formulee, VB isnt a strong point, but i am willing to attemp anything for this, let me explain

Lets say in colum A I have a list of items, in colum B i have a check box, currently the check box is red, if i tpye a x it turns green due to conditional formating. That works fine...onto the problem...

Lets say in colum A i have my items but they are A1, A145, A166, A122 are all the same item, what i want is regardless of where i place the x each check box under coloum B either gets a X and turns green, or just turns green, i dont mind...

I dont think conditional formatting can cut this, though i may be wrong. Ive tried to use the IF function under condtional formatting but ran into problems...

View Replies!   View Related
Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.

View Replies!   View Related
Conditional Formatting: Cells Filled By Red Until The User Enters Text In Those Cells
Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??

View Replies!   View Related
Conditional Formatting Of Cells
I want to add some colour conditional formatting to the cells in column C, dependent on their variance to the figure in column B. I've got the basic gist of applying the rule to the one single cell, but I'm not sure how to apply it to all the cells in the column. I have tried "dragging" it down, but it then just gives me the variance to the original row.

View Replies!   View Related
Conditional Formatting In Multiple Cells
I am having a problem setting conditional formatting for multiple cells.
The first condition of the conditional formatting works for the cells but the second condition does not. There is probably an easy fix for this but I can't seem to find it.

View Replies!   View Related
Find All Cells Having Conditional Formatting
I need to find all cells within a range that have conditional formatting where that formating is:
Formula Is =(cell)="" where (cell) is the cell with the formatting.

As an unpreferred second choice...find all cells within a range that have conditional formatting.

View Replies!   View Related
Conditional Formatting (format All Cells)
I have two colums A and B with a lot of data and want to use Conditional Formatting:

Cell Value Is
equal to
=$A$2 $A$352

What formula do I have to use to format all cells (colored) in the colum B with any existing value in between A2 and A352.


View Replies!   View Related
Conditional Formatting : Highlight THE CELLS
I need to hightlight from A1:A18

In the above cells I have values from 1 to 4

So I need to fill the cell with 4 different colours.

If cell equals 1 = Green
If cell equals 2 = Yellow
If cell equals 3 = Red
If cell equals 4 = Blue

What I do is highlight cell from A1:A18, clik on 'Format', 'Conditional Formatting', I've tried using 'Cells Value is' and 'Formula is'.

View Replies!   View Related
Conditional Formatting With Reference To Different Cells
As shown in the image given below, I have 2 tables. In Image 1, Vacation details are being fetched from a different worksheets. There can be 13 possible values in a particular cell. These 13 entries are,


Excel Jeanie HTMLSheet2 *BCDEFGHIJKLMNOP2Feb-09*Feb-093MonTueWedThuFriSatSun*MonTueWedThuFriSatSun4**************15VSLCOTPH***23456786WMLPLADBE***91011121314157EMLOPMAROTR****161718192021228CTW*******232425262728*9*************** Excel tables to the web >> Excel Jeanie HTML 4


If the value in cell B5 is V then J5 is being colored with PINK background. Similarly if any cell in Image 1 has value SL ( for e.g. C5 ) then K5 is being colored with orange. Similarly there should be 13 different types of color coding for all the above values which I mentioned.


As you can see in Image 1, If I apply these colors to Table 1 only, then the user will not be able to see the value V or SL or any values belong to which date ?

Note - I am doing this for the whole year. But if someone can send me VBA code then I can do it for the rest.

View Replies!   View Related
Conditional Formatting With Values In 3 Cells
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 Replies!   View Related
Conditional Formatting For Range Of Cells
conditional formatting a range of cells where i need more than 3 conditions, so please see below and can anyone kindly construct some vba code for me to do this (And if you have time to explain how it works so i have a good understanding of this)

Range R6 to R299 contains delivery dates
Range AO6 to AO299 is = to the corresponding cells in Range R but formatted to show the Day

Cell AL1 contains a date which changes on a Thursday to show the following week commencing date.

What i am after is code so that Range AO6 to AO299 changes cell colour to the following criteria

less than AL1 (White)
Is between AL1 & AL1+7 (Blue)
Is between AL1+7 & AL1+14 (Green)
Is between AL1+14 & AL1+21 (Yellow)
Is between AL1+21 & AL1+28 (Pink)

View Replies!   View Related
Conditional Formatting On Filtered Cells
Is it possible to apply conditional formating just on visible filtered cells?
If I select (with Ctrl+Shift+Down) all visible (by filter) cells the Rules defined in Conditional formating will also apply to hidden (by filter) cells?

View Replies!   View Related
Conditional Formatting Find All Cells With The Value Of #N/A
I've got this report that I create for my company. This report is currently using conditional formatting; I'am using all three conditions permitted with conditional formatting.

What I need help with is writing a line of VBA code to
-find all cells with the value of #N/A and if the cell directly to the left has a numeric value greater that 0, format both cells to red fill

an example:

Cell g5 is populated with 60 and cell g6 is populated with #N/A (result of a formula).

The line of code that I am seeking would change the fill of g5 and g6 to red.

View Replies!   View Related
Highlighting The Cells Using Conditional Formatting
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.

View Replies!   View Related
If Conditional Formatting (turning Those Cells Off)
what i want to do is say:

If A1="" then color B1 and C1 black, basically turning those cells off

View Replies!   View Related
Conditional Formatting (highlight The Cells Red )
i am trying to highlight the cells red in the b column if a3=d3 (if names match) and if b3<e3 (only if name is "a" should e3 be used to compare with b3). hope this makes sense. i've attached a sample worksheet.

i've tried =if(b3<e3, (vlookup(a3,d3:e33,1,false),true)...but does not work correctly. i need the average weight to remain constant according to the name when comparing, but if i drag the formula down to other cells, the average weight does not correspond to the name.

View Replies!   View Related
Conditional Formatting To Colour Cells
I'm designing a holiday chart in work, and am using conditional formatting to colour cells. The problem is I have more than 3 conditions. Consequently I need to use a macro.

View Replies!   View Related
Conditional Formatting A Range Of Cells
I'd like my conditional formatting to format the range of cells red if the range K2:S2>H2. That is when the range K2:S2 are added together, and if their sum is greater than H2, format cells K2:S2 red.

View Replies!   View Related
Conditional Formatting Set Up On Cells C8 - AH1000
I have conditional formatting set up on cells C8 - AH1000. There are two conditions. The first being, if a date is entered that is in the future, the cell turns red. This works in all of the formatted cells. The second condition turns the back colour yellow if the cell is empty. This does not work consistantly on the cells if column 'C'. The date is entered into the cell, but the back colour stays yello and not clear. The conditional formatting formula is: =Len(C8)=0 Which must be correct because it works on the other cells in the range!

View Replies!   View Related
Conditional Format: Ignore Blank Cells
Excel 2004 for Mac

I'd like to give a conditional format to a range of cells so that only one cell in the range has a gray background. The condition must include three criteria:
1) The cell cannot be blank, AND
2) The cell's value must be the minimum in the range, AND
3) The cell's value must not equal any value preceding it in the range.

My own attempt looks like this for cell J8 in the range C8:AF8:

Problem: This condition works great for all values entered EXCEPT ZERO. When J8 is the first zero in the range, if it is preceded by a blank cell anywhere else in the range, it fails the third criterion (J8$C8:I8) because Excel treats the blank cell as though it's a zero. Result: no gray background.

How can I get Excel to "ignore" blank cells.

View Replies!   View Related
Finding Cells Colored By Conditional Formatting
This works if a cell is simply formatted "red" but it does not work if the same cell is colored "red" by virture of conditional formatting. How do I find cells that have been set to color "red" by conditional formatting

This prevents saving the workbook without entering all the data.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Row As Integer
Dim Col As Integer
For Row = 1 To 450
For Col = 1 To 50
If Worksheets("Sheet1").Cells(Row, Col).Interior.Color = RGB(255, 0, 0) Then
MsgBox "Every cell must have data."
Cancel = True
End If
Next Col
Next Row
End Sub

View Replies!   View Related
Move Merged Cells With Conditional Formatting?
I have a spreadsheet as per the enclosed attachment. The top date is always Today's date, regardless of when the spreadsheet is opened. The rest of the dates are Today's date plus 1, plus 2, plus3, etc. Therefore, the dates are moving up the table, day by day. The Saturdays are linked to the correct dates by using the =IF(WEEKDAY(F6)=7,"Saturday", "") function, so they are moving up the screen as well.

The horizantal lines between the weeks move correctly due to Conditional Formatting (thanks to bpeltzer for that tip).

I would like to merge the blocks of cells that make up a week in such a way that the merged group of cells moves up the table correctly with their corrosponding dates.

I have manually merged the top row of cells in the attachment to indicate what I intend to do, but tomorrow, when the dates have moved up one, it will look all wrong again.

Is what I want to do possible, and if so, how is it done?

View Replies!   View Related
Conditional Formatting On Cells With Data Validation
I have column of cells, both of with have drop down menus to select information from. I have an adjacent cell to display an error based on conditions. Is there a way to have conditional formatting to apply to the cells with Data Validation to change the color, based on the error. I have tried using the Conditional Formatting options but it still does not change the color of them. I believe i have to use the formula option, but not exactly sure what to put, i know a if statement could work but unsure on how, there are only two conditions that i would need for it to apply, if the adjacent cell has an error, and when it does not. Is this possible?

View Replies!   View Related
Adding Cells Based On Conditional Formatting
I am using conditional formatting to make cell background's go red if the data satisfys some criteria

Now I want to add the values in those red cells, can anyone make a suggestion as to how I can do this.

View Replies!   View Related
Add A Single Expression For Conditional Formatting Across All Cells
I have a large spreadsheet in which I have reached the limit for conditional formatting. (i.e. 2050 rows)

To resolve this I can add a single expression for conditional formatting across all cells. This means I need to create a more complex expression.

I effectively have two types of conditional formatting that are very similar. I have the standard “Yes”, “No” & “A/L” which I wand to shade accordingly. I then have alternating columns of “ Yes “, “ No “, “ A/L “. I have introduced spaces to stop these values from being counted by another formula but still display yes and no. I want the shading to be the same in both cases.

I have built the following expression which does not seem to work.


The two components of the expression work separately:



View Replies!   View Related
Conditional Formatting To Show When Two Or More Dates In A Row Of Cells Are The Same
I want to use conditional formatting to show when to or more dates in a row of cells are the same.

i.e. A B C D E
16 Dec 29 Jun 16 Dec 20 Apr 15 Dec Etc

How do i do it anyone so that it automaticaly changes as the dates change when new data is added?

View Replies!   View Related
Conditional Formatting Set To Paint The Cells A Certain Color
I have conditional formatting set to paint the cells a certain color if the values are >0 and a diff. color if they are <0. If i enter the value manually everything is fine but if a use a formula to do the calculation there is no formatting.

View Replies!   View Related
Comparing Adjoining Cells- Conditional Formatting
I would like to compare each cell in a spreadsheet to the cell on its right.

If the numeric value of a cell is less than the numeric value of cell on its right, I would like to highlight the cell on the right light red.

Maybe a better way of putting that is if the value of a cell is greater than the cell on its left, highlight it light red (The problem I foresee with this formulation is in the first row where there are no cells to the left).

View Replies!   View Related
Conditional Formatting Code To Shade Cells
In B16:T24 I have cells that need shading depending on what code I put in them. I have 5 codes P, BH, S, ML, HD

I would like the cells to change colour, when I put P in them I want them to change to blue, when I put BH in them I want them to change to green, S I want to change to red and so on.

View Replies!   View Related
Conditional Formatting Dependant On A Range Of Cells
On attachment, I am trying to highlight values in column AA dependant on any values appearing in range AF4: AQ8 less 15 minutes. eg at present the value in AA29 is 13:22. I would like this cell to highlight as it is greater than 15 minutes before AH4 at 13:25.

The purpose of the sheet is to highlight any times in column AA that fall within 15 minutes of the start of a major meeting. These major meetings are pulled from another sheet, and a drop down box is used on that other sheet that then transposes the major meeting times to this sheet. I have tried an "Or' statement in a conditional format, however an error message appears around not using a range in a conditional format.

View Replies!   View Related
Conditional Formatting: Change Another Cells Color
Is it possibe to change another cells color (Fill) based on what is placed in that cell? Example: A1 =if there is an "x" in A2 highlight A1 Green, but I have information in A1 also. Or if I had a different cell say= if there is an X in A1 Highlight A1 Green ect.. This key is to highlight a cell that I have information in based on a different cell having an X in it. If ther eis no X in the cell I have indicated, nothing needs to happen.

View Replies!   View Related
VBA For Conditional Formatting Of Cells With Different Month Values
I 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.

View Replies!   View Related
Conditional Formatting Of Cells Between Date Range
I looked through the format link: Conditional formatting page on Ozgrid and was unable to figure out what I want to do.

What I want to do is check a range of cells which have dates in them, and then if they are between such and such dates, they will turn a certain background color. But by "such and such" I mean, between the dates entered in two different cells.

So in the range of A1:A10, if any of those cells are between b1 and b2 then apply color1. If any of them are between b2+1 and b3, then apply color2.

If it's not possible tell me but I think it should be as I can do it with conditional formatting. I need to have 16 conditions though. What it is is a chart with projects in rows and dates in columns. The dates often change and I want to rearrange the entire schedule visually just by changing the date in another place. (I change the date in the target cell, what I call b1 above, and b2-10 are calculated based on that date). Hope that's enough information.

View Replies!   View Related
Conditional Formatting To Highlight Cells With Formulas
I have searched for a while but can't seem to find where the original thread is at. Could someone point me in the right direction?

View Replies!   View Related
Conditional Formatting Using Formula For Data In Cells
I am trying to conditionally format some data in cells A3:A6 (shade the cells) depending on whether a number in B1 is 1 (green), 2 or 3 (blue), or 4 (red).

View Replies!   View Related
Conditional Formatting - Based On Another Cells Contents
I have a worksheet that I've applied conditional formatting to which works very nicely apart from when I use the formatting and apply it to a cell based on the contents of another.

What I don't understand is when I put a formula in cell b1 to read =$a1="yes" and format accordingly it works. So when I type in "YES" in to cell a1 ,cell b1 is highlighted. When I type in "NO" the B1 cell isn't highlighted which is what i would expect. IF however I delete what ever is in cell A1 the cell B1 is still highlighted.

View Replies!   View Related
Conditional Formatting If Cells Match Criteria
I would like to have excel check if eight of ten of cells in a row are above a certain criteria, then if they are format the tenth cell to be excel checks the ten cells above and if eight of them are above a certain number then that cell shades red.

View Replies!   View Related
Copyright © 2005-08, All rights reserved