Free Microsoft Excel 2013 Quick Reference

Excluding Blank Cells in Conditional Formatting

Currently using conditional formatting to colour cells of dates when they go out of date. Im using a between function and less than function. This is what causes the blank cells to be coloured, can i set it up so blank cells are excluded?

Chris


Post your answer or comment

comments powered by Disqus
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?

Thanks in advance

How do I exclude blank cells in diagrams. If I have an area of data and among
these data some is blank. How do I get excel to not display these data as '0'
but just to skip the cell.

Hi All,

Hopefully a simple one for you:

I have a UD function, where the user selects 4 cells. I'm trying to get the function to simply match each of these against each other and place a True in a variable if any of them match with each other.

The way I have done this is probably not the best way:

If Source1.Text = Source2.Text Or Source1.Text = Source3.Text Or Source1.Text = Source4.Text Or Source2.Text = Source3.Text
Or Source2.Text = Source4.Text Or Source3.Text = Source4.Text Then

Results4 = True
End If
[Code/]

The problem I get - is that this looks at Blank cells and compares against other blank cells - which I dont want to happen.

Is there any way I can exclude blank cells in my code?

Thanks in advance.

Can someone please tell me what is the formula to setup in Conditional Formatting for this:

Within only Column A, cell change to color yellow if it is not blank. Everything blank cell leave it unchange.

Thank you!

Hi,

How can I enter a formula in conditional formatting that gives an orange background to a cell if the cell is blank and if the cell also is in the column of the current month (row one has the month)?

That is, how can I write the following formula?
Thanks!

/Daniel

I am having trouble with conditional formatting and blank cells. I don't want the conditional formatting to work for blank cells.

I have a answer key and test answer worksheet (1 and 2). Because I can't reference different worksheets with conditional formatting I have referenced the different worksheet in a cell and then reference that cell (within the same worksheet) in the conditional formatting. The conditional formatting is working correctly if the answer is right or wrong but if the answer on the key page has not been inputted yet the conditional formatting is already working on the test answer page saying the answer is wrong even though it is not known yet what the answer is. Until an answer is put in on worksheet 1, I don't want the conditional formatting to work on worksheet 2.

Any thoughts?

I have problems with conditional formating registering blank cells as having the value 0. This means I can't set 0 in the range of any conditions as it flags blank cells! I'd appreciate any help you can give on this...

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

Please see the sample I have attached (very basic example).

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

What needs to be done for this to happen?

Thanks,
Alan

Hi,

Please help me as soon as possible. I need to count the blank cells in one column (the Date received column) that also meet a date condition in another column (the Transfer Date column). I also need to add up all the account balances (in the Account Balances column) for the accounts that were just counted.

I attached an illustrative workbook. the first tab has the raw information. The two pieces of analytical information sought are on the second tab. First, I need to identify and count the accounts that were received more than 120 days ago and for which there is not tranfer date entered. Then for those identified and counted accounts, I need a summary of the corresponding balances.

I just can't figure this out. I've tried conditional formatting, countif, IF combined with COUNT, and more. I get it working for partial steps, but not enough to complete the task.

I would greatly appreciate some help here, asap. Thanks. God Bless.

I need to highlight every other row in a worksheet for readability, which is easy to do with a conditional format using the ISEVEN(ROW()) function. But this overrides any manually-entered cell color. Is there a way to include a test for no color (or any color) in the conditional format so this won't apply if the cell is manually colored?

Hello

Using conditional formatting to color duplicate cells in one column. If the formula does not return anything the cell is coloured because of other blank cells in the column. Formula in J3 is =RIGHT(F3,4) data in F3 is alphanumeric formatted as Text.
I have tried Condition 1 as =IF($J$3:$J$200,J3)1
Can I stop the color appearing in blank cells.
Thanks

I would like to know if there is a way to add the option of bold outlining
cells using the conditional formatting function. I think I understand that
it cannot be used because it changes the cell height but I still would like
to add it in any way possible.
I cannot use if then statements in this case.

Excel2003 ... My Cols are A thru H =

Date - Mon - Tue - Wed - Thu - Fri - Sat - Sun

Cell A2 = a date (or is blank (empty)) ... format = mm/dd/yy
Cells B2 thru H2 contain Conditional Formatting as follows:

Cell B2 ... =if(weekday(A2,2)=1
Cell C2 ... =if(weekday(A2,2)=2
Cell D2 ... =if(weekday(A2,2)=3
Cell E2 ... =if(weekday(A2,2)=4
Cell F2 ... =if(weekday(A2,2)=5
Cell G2 ... =if(weekday(A2,2)=6 ... this one is a problem???
Cell H2 ... =if(weekday(A2,2)=7

All above are working ok, except ... =if(weekday(A2,2)=6 ... this formula in
Conditional Formatting is setting off the Conditional formatting when Col A
reference cell is BLANK (empty) ... Anyone know what gives?

Note: if I place "=6" in any of the other Cells it also sets off the
Conditional formatting when Col A reference cell is BLANK (empty)???

Thanks ... Kha

Hi guys,

Test file

In the test file above I'm trying to do a conditional formatting of a cell depending on the column/row values entered in the green columns. I only succeeded doing so for one entry, but I need to make it work for several values (theoretical max. = 150), so several cells should 'light up'.

Could somebody help? Or has someone a different approach?

I am trying to use conditionally format row of cells if the value of the cell in column A is between 1 and 5 but not blank. The simple expression,

=INDIRECT("A"&TEXT(ROW(),"0"))<=5

produces "TRUE" and in conditional format will format all rows, unfortunately also those with 0 or nothing in column A.

The expression,

=AND(INDIRECT("D"&TEXT(ROW(),"0"))>0,INDIRECT("D"&TEXT(ROW(),"0"))<=5)

does produce TRUE only if the value of the cell in column A is between 1 and 5 but not blank, but does not work in conditional formatting, at least in Excel 2007.

The expression,

=(INDIRECT("D"&TEXT(ROW(),"0"))>0)*(INDIRECT("D"&TEXT(ROW(),"0"))<=5)

also produces the value 1 (TRUE) only if the value of the cell in column A is between 1 and 5 but not blank, and it does work in conditional formatting.

There are other alternatives to solving the problem, such as using the combination of

=INDIRECT("D"&TEXT(ROW(),"0"))=0 [check the stop if true box]
=INDIRECT("D"&TEXT(ROW(),"0"))<=5

BUT why doesn't the AND expression work? Some solutions in the forum do suggest the the use of AND and are reported to work. I downloaded a the example file AF010235700.XLSX and the AND expression doesn't work for me in that file either.

Am I missing some setting in my computer or program? I have SP3 installed. http://www.excelforum.com/images/smilies/confused.gif

I was just wondering, if somebody knows... I'm applying conditional formatting and wanted to put a border around a cell. It works, but I would like to have a thicker border, which doesn't seem to be available in conditional formatting. It only gives the basic border options of solid, dotted, dashed, etc. lines. But no option to modify thickness of the border? The plain thin line is not eye-catching enough for me in the huge, colorful spreadsheet I'm using.

Is that correct? Or if I can change it somewhere, maybe somebody can point me in the right direction?

Thanks so much!
Sylvia

Hi,

I want to conditionally highlight (in red) the blank cells in one column (the Date received column) that also meet a date condition in another column (the Transfer Date column). I also highlight all the corresponding account balances (in the Account Balances column) for the accounts that were just highlighted.

I attached an illustrative workbook. the first tab has the raw information. The second tab contains two pieces of analytical information, which you can ignore, but it might help you with the conditional formula. This post thread from earlier today might also be helpful (http://www.excelforum.com/excel-2007...er-column.html).

I want to highlight the cells in the Transfer Date column for accounts that were received more than 120 days ago and for which there is no tranfer date entered. Then I also want to highlight the corresponding balances.

Thanks. God Bless You.

Hi there!!!

I am faily good with excel formulas but a complete novice with macros and VBA.

I am working in excel 2010 and have created a worksheet which color codes timelines i.e. I have put in conditional formatting on a range of cells where 14 criteria are evaluated and 1 color is defined for each criteria.

In this sheet the color for each criteria has been defined by me however I would like to give user the option for choosing the color for each criteria.

I want to use a macro to produce a summary on the [Master] sheet. The summary will reflect items (tasks) on the subsequent sheets which have a blank cell in the column matching cell [Master]A1. Further, I want to exclude tasks defined with "qtrly" in column C of each sheet in all months except Mar, Jun, Sept, and Dec. The previous summary data will be cleared at the beginning of generating a new summary.

The idea is to be able to quickly identify incomplete tasks and their respective owners at a glance. Thank you for any assistance you can provide.

Hey all,

I'm trying to run down through a very long/variable Column Y and add a SUM formula of the cells in Columns J:X to all blank cells in which the row also has an account number in Column A. (I've attached a truncated example of the spreadsheet). Basically, if there is a blank spot in the totals column that also has an account in column A, I need to sum a bunch of the values in between.

Here's a code I've written so far. I tried to add an active cell formula to this to allow the macro to work on any column but had even worse results. Any help would be greatly appreciated.


	VB:
	
 FillEmpty() 
    Dim rCell, rColumn As Range 
    Dim lLastRow As Long 
     
    lLastRow = Range("Y65536").End(xlUp).Offset(0, 0).Row 
     
    Set rColumn = Range("Y1:Y" & lLastRow) 
     
    For Each rCell In rColumn 
        If IsEmpty(rCell) Then 
            If rCell.Offset(0, -25).Value  Empty Then 
                rCell.Formula = "=SUM(J:X,rCell.row)" 
            Else 
                 
            End If 
        Else 
             
        End If 
    Next rCell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you,

Marcus

I am trying to post the results of one cell to the next blank cell in a different row. For example; I need the results in S3 to post to the next blank cell in row P. I tried the worksheet_change code below but that will only work if I make a manual entry in S3 and does not work if I use a formula in S3 to perform the calculation. My formula in S3 is =MAX(L5:L505). When I hit F9 to recalculate I need the results of S3 to post in row P each time. Perhaps there is a formula that I can use as opposed to VBA.


	VB:
	
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Address  "$S$3" Then Exit Sub 
     
    Application.EnableEvents = False 
    Range("P65536").End(xlUp).Offset(1, 0).Value = Target.Value 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


How to highlight a row and its previous row for every blank cell in a column

I could only highlight the row containing the blank cell, but not the row above this. Help with my code. Offset doesn't seem to work for me in this scenario.


	VB:
	
 ConditionalFormatter() 
    [F3].Activate 
    With Range([F3], [F65536].End(xlUp)).EntireRow 
        .FormatConditions.Delete 
        .FormatConditions.Add Type:=xlExpression, Formula1:= _ 
        "=$F3=""""" 
        .FormatConditions(1).Interior.ColorIndex = 6 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,
How can i test in VBA if the active cell has conditional formatting?

Apparently

	VB:
	
ActiveCell.SpecialCells(xlCellTypeAllFormatConditions).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
will hightlight all cells that have Conditional Formatting, but i cant figure on how to test the extive cell only.

Thank you,
Stefan

The code segment from "Get Around Excels 3 Criteria Limit in Conditional Formatting" works perfectly if I type the variable directly into the cell, but not in a range of cells.

That is to say, if I copy the source cell to a range of cells I receive a "Run-time error 13: Type mismatch" error".

Is there a way to get around this?

This is the code segment I've tried in the worksheet.


	VB:
	
 Range) 
    Dim icolor As Integer 
     
    If Not Intersect(Target, Range("b2:q53")) Is Nothing Then 
        Select Case Target 
        Case "IBBCH", "ibbch" 
            icolor = 36 'light yellow
        Case "OBBCH", "obbch" 
            icolor = 34 'light turqoise
        Case "OBBRDG", "obbrdg" 
            icolor = 35 'light green
        Case "LNCH", "lnch" 
            icolor = 53 'brown
        Case "BRK", "brk" 
            icolor = 15 'gray-25%
        Case "AV", "av" 
            icolor = 10 'green
        Case "AS", "as", "med", "MED" 
            icolor = 3 'red
        Case Else 
             'Whatever
        End Select 
         
        Target.Interior.ColorIndex = icolor 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



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