Free Microsoft Excel 2013 Quick Reference

Conditional Format when using borders

I have written a code to extract information from a program and present it in a tabulated form on an Excel spreadsheet. The tables vary in size each time the information is updated and I would like to know how I can get the Macro to only put borders around the information within the table. For example, I can create set borders for 7 rows, but if another row is added, can I get it to automatically put a border around that?

When I use "show pages" to drill down the data into pages from Pivot table, I
find the conditional format on pivot table is missing. I have to make
conditional format again to every page.

How to keep the conditional formatting when I use "show pages" from pivot

When I use "show pages" to drill down the data into pages from Pivot table, I
find the conditional format on pivot table is missing. I have to make
conditional format again to every page.

How to keep the conditional formatting when I use "show pages" from pivot

I have set conditional formating to highlight public holidays when a series of dates are calculated from a cell input for pay period week-endings. It works perfectly from a manual input, but, as I require data validation for pay period day endings and also selecting weekly paid or fortnightly paid, I have created a named list that gives a limited number of dates in the list for selection. That part works perfectly, but the conditional formatting does not work! when a selection is made from the list. So - everything works with manual input but does not work with list input. Can anybody suggest a work-around for this?

I have a problem with conditional formatting when using percentages which I
will try to explain.

I am using "Format all cells based on their value"
Format Style = Icon Sets
Reverse Icon Order is selected.
Red traffic light when value is >= 90 percent
Orange traffic light when < 90 and >= 80 percent
Green traffic light when < 80

All cells have been formatted as percentage.

However the conditional formatting applied is not as expected.
A1 = 50%
Expected result = Green
Actual restul = Green

A2 = 80%
Expected result = Orange
Actual result = Red

A3 = 70%
Expected result = Green
Actual result = Orange

If I change this to numbers, then the results are correct.

Any thoughts appreciated


I have a coding problem.

Basically, i have cells that, if they are similar they change from grey to red (font), that is done with conditional formatting.

When using that data to create another sheet with a macro, I want it to put up a message, if the current font is red, however, when i use the cells(x,y).font.colorindex with an if statement, it doesn't work!

I current have it as,

if cells(3,5).font.colorindex = 3 then uhoh = 1
no matter what i do the message box comes up blank. unless i put in a value for uhoh earlier in the code example uhoh = 4, then it will display 4 in the msgbox.

When i select the cell that has red font, in 'format cells' window, it shows font as grey, even though it is RED... Please help...

I have a cell that needs to be conditionally formatted when the cell (which is a date) is 2 days before current date, or less.

If 08/09/2010 was in the cell, then it would be formatted accordingly, but if it was 08/05/2010 it would not be.

Any thoughts?

Hi All,

I have conditional formatting in one of my files, which changes the color of the cell font and background when a related cell satisfies a certain condition. When a symbol is entered into the cell with conditional formatting, however, the conditional formatting seems to be negated! Even if I change back to the font that the cell had originally (usually Arial), all text from the symbol onwards becomes black, no matter the font color of the conditional formatting. Is there any simple way around this? I would still like to display the symbol as a symbol, but it will be mixed with other text depending on what the user puts in.

I have included a simple example, with the name column having conditional formatting applied to it depending on the value in the state of being column. As to the validity of the example, please assume several people were inspired by Prince to use a symbol as one of their middle names.

By the way, I am using Excel 2003.

Thank you!

Hi guys,

I have the following code which I'm using to find the last 10 rows that have something in them, and then copy the data into a summary table. The code works fine. The only problem is when the data gets pasted into the summary table, for some reason Excel is applying conditional formatting to some of the cells in the summary table. I have no idea why because the cells that I'm copying from don't have conditional formatting in them (although some others on the worksheet do).

Does anyone know why this is happening? I've noticed the same thing on some other macros that I've written and used to move data around my spreadsheets. I don't know if it's the code I've written or something else with Excel in general?

Sub LastTenEngines()

    Dim EngineArray(10, 11) As Variant
    Dim i As Integer
    Dim lngCols(11) As Long
    Dim vntCol As Variant
    Dim lngRow As Long
    Dim lngCol As Long
    Dim SumRow As Integer
    Dim SumCol As Integer
    Application.ScreenUpdating = False
    For Each vntCol In Split("C,BF,BI,BK,BM,BO,BQ,BS,BU,BW,BY", ",")
        lngCol = lngCol + 1
        lngCols(lngCol) = Range(vntCol & "1").Column

    lngRow = Cells(Rows.Count, 6).End(xlUp).Row ' get last row
        ' work way back up the rows populating array from last element
    i = 10
    Do While lngRow >= 9
        If Len(Cells(lngRow, 6)) > 0 Then
            For lngCol = 1 To 11
                EngineArray(i, lngCol) = Cells(lngRow, lngCols(lngCol))
            i = i - 1
        End If
        lngRow = lngRow - 1
        If i = 0 Then Exit Do


For SumRow = 1 To 10

    For SumCol = 1 To 11

        Worksheets("SUMMARY").Cells(6 + SumRow, 1 + SumCol) = EngineArray(SumRow, SumCol)

    Next SumCol
Next SumRow

End Sub



I have a spreadsheet containing amongst others, 10 lists with conditional
formatting on every cell (e.g. border,pattern etc).

I use VBA code to print these lists but have noticed that when I print more
than 1 sheet, only the first few lines print with the con format displayed

I also noticed that the number of lines printed correctly correspond to the
number of lines in the first sheet i.e. if the first sheet contains 10 lines
and the second contains 20, only 10 lines on the 2nd sheet will print
correctly. If I have more lines on the 1st sheet than the 2nd sheet, all
lines on the 2nd sheet print correctly!

Is this memory related and if so, any suggestions for fixing it?

Thanks for any help.

Lawrence Kritzinger

I am using conditional formatting to change the fill color of a cell if the value does not equal a reference cell. The problem I am having is that the reference cell will be blank after data is entered into the cell with the conditional formatting. Obviously, when the data is entered the two cells are no longer equal and it changes color based on the condition being true (blank<>data entered). How can I keep it from changing color if the reference cell is blank or only allow it to change color if the reference cell has data entered in it?

I need to test for some values in Excel. For coloumn D12:D16 i need its font to be bold if there's sum is not equal to 100. used conditional format initially saying:
Formula is: =SUM(D12:D16)100 and changed the format accordingly.
it worked.
tried doing the same using the code as i was supposed to use this validation on some other page so, i took the cell say (12,13) M13, applied the fomula in it as "=SUM(D12:D16)" now in VBA i worte the code as :

If Cells(12, 13)  100 Then 
    Cells(12, 4).Font.Bold = True 
    Cells(13, 4).Font.Bold = True 
    Cells(14, 4).Font.Bold = True 
    Cells(15, 4).Font.Bold = True 
    Cells(16, 4).Font.Bold = True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
this time, even when the sum is 100(values in D12:D16 is 0.1,5.5,1.8,76.7,15.9) it goes in the loop!!!
seems VBA has some issues in rounding the values or so.
can anyone look into this?

How to get the Font Color, Fill Color of cells with Conditional Formatting for more than 3 criterias using VBA?

Hello Board,

Is it possible to get the Color Codes like Color Nos for cells which have conditional formatting..
When we try using the .Font.Color or .Font.ColorIndex it gives the number only for the color which is applied manually and not for any color via conditional formatting.

If i have more than three criterions in my conditional format, then is it possible to get the Font Color, Fill Color i.e. Interior Color and if possible other aspects such as Font.Italics or Font.Bold too using VBA?

Will the code be able to read the Font Color Code and Fill Color Code no from individual cell with conditional formatting rather than looking at a Range, In case, if we were to provide a cell range for example A2 where the Font Color is red and the Fill Color is Yellow and in cell A3 Font Color is Blue and the Fill Color is Orange then is it possible to just to give the Range as A2 or A3 and get both the well as if the other aspects such as the Font Bold and Italics.

I did a search on the board for this, but I gotta say, It flat out confused me...I didnt find a post that was similar to what im trying to do...and the ones i did find I could have sorta used, but I had no idea how to re-edit it hahah....could someone please give me a hand here?

I already have 3 conditional formats being used but need 2 more.

my ranges are from 3 to 3822 in all my columns..

Basically I need the following:

the 4th conditional format: If any cell in column "B" equals ERI......then ...look in column Y in its adjacent cell, and if the number in the cell in Y is >30 then highlight the row of cells (not the font) from A to AB in green.

the 5th conditional format: Same as above but look for FUGEM in column B (instead of ERI)

Maybe it could all be done in the 4th conditional format??..Saying look in column B for ERI or FUGEM and then look for the >30 and make green??

so for example say B445 has ERI.........and Y445 has a value of 52......then A thru AB would highlight green.

(also...when i attempted to go into VB, i did not see any code writen down....shouldnt something be there if i already have 3 conditioanl formats on the worksheet already???

I am attempting to run a macro that will make conditional formatting I have applied permanent, and then remove the conditional formatting from the cell. My application for this is a master fantasy football cheatsheet, with multiple columns of the same player names ranked in different orders. When a player is drafted I type their name in cell A1, and conditional formatting is setup on all cells with names such that all cells containing that name (presumably once in each column) will be highlighted/bolded/etc. For example, if I typed "LaDainian Tomlinson" in cell A1, I would be able to find his name in each list/column (from the conditional formatting that will make it stand out), and then run the macro to make this formatting permanent (show he has been drafted), and remove the conditional formatting code, leaving the effects. I found a macro online to do this at the following link, but am having some issues:

When I run the macro on any cells not containing conditional formatting, I get the message: "The formatting based on the conditions in the range (whatever is highlighted) has been made standard for those cells and the conditional formatting has been removed."

This messsage is precisely what I am trying to do, however, when I run the macro on a cell with conditional formatting (when it would actually be beneficial), I get "Run-time error '13' Type Mismatch"

When I go to debug, the error appears to be on the line "If CDbl(rng.Value) = CDbl(FC.Formula1) Then"

I am using Excel 2003, and unsure how to proceed. I would be the Fantasy Football king if I could get this macro to work. Can anyone solve the mystery?

If that cell to be formatted is A1, how about:
Formula is:


(Top one formats if the formula returns "". Bottom one if the formula returns
that number.)

I like the second formula--but that's a personal preference. I find it easier
to see those "" when they're next to what caused them.

But I think the real answer is to use the one you like and be able to understand

Father Guido wrote:
> Hi,
> I'm trying to make a conditional format when the result of a formula
> results in a value being entered in a cell.
> I'm using Excel Xp and my formula is as follows.
> =IF(F160="Need Info",TODAY()-S160,"")
> I've tried using formulas like >0, "", BLANK etc. but they all
> seem to fail. All cells are formatted, even entirely blank cells, as
> if the condition has been met.
> BTW, is this formula better strategically than the above formula.
> =IF(F160"Need Info","",TODAY()-S160)
> Just curious.
> TIA!!!
> Norm


Dave Peterson

I originally posted the following in the General Forum:

I would like to use the 'Conditional Formatting' function on a text box. I would like for the text box on this Sheet2 to change colors when I've typed something into Sheet 1. Can conditional formatting be used on text boxes? If not, is there another way to tell Excel what I am trying to do?

Reply to my question:

teylyn -
'You can't use conditional formatting on a text box. To dynamically manipulate the textbox colors you would need to use VBA.

Start a new thread in the Programming forum if you want to pursue that option.'

New Question:

So I guess I can't use conditional formatting on a text box. Can someone help me with VBA. I've never used this and don't know where to start.


Im working in excel.
In row 3 i have 12 goals (D-O) for my full time associates.
Under that, I have my Full Time associates listed with their results under the goal.

In row 17 i have 12 goals (D-O) for my part time associates.
Under that i have my Part Time Associates listed with their results under the goal.

In row 24 I have 12 goals (D-O) for my D Status Associates
Under that i have my D Status associates listed with their results under the goal.

They all have a Total column in column Q
I want the associate total to turn green if the associate meets their goal, but here are the two catches. If an associate is hire mid year, two things happen that mess up my current formula used in the conditional formatting.

1) when my macro to insert a row is run, it puts a new row into whichever named range (Fulltime, Parttime, or DStatus) and the formula is thrown off because the reference rows change.


2) when the associate is hired mid year, they get values of " '--------- " in every results cell so that they arent counted in the count function and don't turn red because of a 0 result vs goal. The problem here which is solved in my first formula, but i simply dont understand how it works, is that their total should only turn green if the month goals, aera dded, but only the months they are actively employeed. IE They are not added from feb-jan if the associate was hired in aug because then the goals that apply to them in the real world are only aug - jan.

Here is the conditional formatting im using currently...

Please feel free to look at the attatched file to see what i mean
Please help me understand #2, and fix #1


I'm an Excel 2000 (SP-3) user and not much of a coder. I use excel to set up webpages for a little database that I am running. My problem is that I want to use some conditional formatting on the hyperlinks to indicate the status of information in the database. Like this:

Green - all info is there
teal - some info missing
orange - little info available
red - no info

This conditional formatting is used on some large tables of hyperlinks and that works quite well in excel, but when I export it to a web page, then all the conditional colors are completely lost. Only the standard color is there.

Here is an example where the conditinal formatting replaces the green color in excel with red but in the html code it is still just green:

  <td colspan=2 class=xl10020314 style='border-right:.5pt .5pt
  border-right:.5pt solid black;border-bottom:none;border-left:.5pt solid black'><a
  href="Brown.htm#Animal"><span style='color:green;font-family:Arial, sans-serif;
The code for red seems to be overwritten by the green. Have I missed something? or forgotten to set something up?

I currently have a procedure that allows the users to click on a button
to move a selected row up or down in a list. The problem I'm having is
that when they move the rows down the conditional formating on the
cells get screwed up. Below is an example of what happens when I try to
move row 1 down to row 2.

Conditional Formatting Formulas Prior to Move.
(There is actually 2 conditions in each, but for simplicty I just used
Row 1 = "=$A1"
Row 2 = "=$A2"
Row 3 = "=$A3"

Conditional Formatting After I moved row one down to row 2.

Row 1 = Formatting is gone
Row 2 = "=$A4"
Row 3 = "=$A5"

Here is the part of the code that I use to move the rows. The problem
seems to occur when I use the insert statement.

MoveFrom = Selection.Row
Rows(MoveFrom + 2).Select
Selection.Insert shift:=xlDown
Rows(MoveFrom + 1).Select

The thing that makes this more frustrating is that I don't get the same
problem when the user moves a row up. It's basically the same logic.
Any help would be greatlty appreciated.


I'm trying to use conditional formatting for a cell where the cell will turn green if the value of the cell =

=VLOOKUP(B5,'[Corp Ovhd 6.08_Final.xls]summary'!$F$4:$I$150,4,FALSE) and the cell will turn red if the value of the cell is not equal to the formula above.

Unfortunately, I don't think you can use conditional formatting when linking to a cell in another file.

Does anyone have any ideas for a way around this?



I use this formula in the conditional formatting box to check if a certain cell is blank. =IF(A2="",TRUE,FALSE)
If it is, I set the color of the cell with conditional formatting to blue. However, this code does not work and even when A2 is blank the color of the cell with CF does not change to blue. What am I doing wrong?


I am trying to write a macro that is to be used in 2003, however, I have 2007 on my computer. I originally recorded a macro, and then applied the conditional formatting in order to get the code. However, when I went to my colleagues computer and tried to run it, I received an error (I believe 438). I looked online and tried to use proper syntax for 2003 but am getting run-time error 438 on my computer (2007). Here's my code. Essentially I want to format it if it's not equal to 0 (so, greater than or less than 0).

 With Worksheets(1).Selection.FormatConditions.Add(xlCellValue, xlGreater, "=0")
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16776961
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
End With
 With Worksheets(1).Selection.FormatConditions.Add(xlCellValue, xlLess, "=0")
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16776961
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
End With

I'm after a macro to temporarily disable conditional formatting for a range of cells when printing.

Or even just temporarily set the cell background colour to "no fill" and then have it restored after printing.

The reason for this is I'm creating a bending schedule for steel reinforcement for concrete, and would like to warn the user that an invalid dimension has been entered using conditional formatting, however if the user really wants to continue I don't want the conditional formatting to show on prints, is this possible?

Many thanks.

I have used conditional formatting in one of my spreadsheets.

I save the workbook immediately after applying it.

At least three times when I reopen the workbook, the conditional formatting
has disappeared.

What gives?