Free Microsoft Excel 2013 Quick Reference

Conditional Format for Cell Borders

I need an experts opinion here.

I know the VBA code to create a border around a cell. How do I condition the code to have the border present only when the cell contains data. ie, cell empty = no border, cell with data = border. In this case, contents of the cell are irrelevent.

2nd question: on an autorun macro, is there a comand that will pause the macro so I can paste a jpeg file into the worksheet then continue running the macro till the end?

Post your answer or comment

comments powered by Disqus
How do I set the conditional formating for cell D2 based on the value in cell

conditional formatting works very well with numbers. Is there a way to
conditionally format a cell that contains words? For example if the cell
contains "confirmed" would be green and "decline" would be red - works. What
about if the cell conatins "confirmed" but also something else in that cell?

I am wanting to set conditional formatting for cells A1 through H1. I want the conditional formatting to be determined by the Value in cell I1. Can you tell me if this is possible. I am wanting to set the formatting so if cell I1 is "Complete" the font color for cells A1 throught H1 will be white so they will not be visible. If the value in cell I1 is anything other than "Complete" the font color will be black and cells A1 through H1 will be visible.


does anyone how I can use conditional formatting for cell swhich do have dependents and or precedents?

Thx in advance


This is probably easier than I think...? Maybe this shouldn't even have been
posted under Programming.

I have a label - created from the toolbox - that shows a value (taken from a
specific cell). If this value is 50% of another cell-value, I'd like it to
change the background color to yellow. And if 100%, I'd like it to change to

Like a dashboard. Same principle as Conditional Formatting for cells, but
for labels instead.

Any help is highly appreciated,


I have searched this forum (and others) for the solution to this problem but without success. I hope somebody can help.

I have been using conditional formatting for a project in Excel 2007 but as the end users are using Excel 2003, I have had to switch to the following VBA solution as my requirements exceed the standard 3 available conditions. I have looked at using custom formatting but I need to format the cell colour rather than just the font colour.

    Dim icolor As Integer 
    If Not Intersect(Target, Range("C19:IV384")) Is Nothing Then 
        Select Case Target 
        Case "0.5", "1", "U" 
            icolor = 38 
        Case "C", "M", "P" 
            icolor = 40 
        Case "A", "S", "D" 
            icolor = 36 
        Case "L", "UP", "C/E" 
            icolor = 35 
        Case Else 
        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
I am hopeful somebody can help me modify this VBA to achieve the following:

* When the macro is run on one worksheet, formatting and values are replicated simultaneously on another identical worksheet (not necessarily vice versa).

* As well as formatting cell colour when containing a value, a border should also be added with different colours for the top, bottom, left and right border.

* When the cell contains no value, the borders should return to how they were previously.

Many thanks in advance for any advice or solutions.

I am looking for a conditional format for the following scenario.
If A2 has a value less than 1.5 and A3 has a value less than 2 then A1 will show green or whatever. Both of these values have to be met seperatly or A1 will remain unchanged. I just dont know how to put the AND in the formula to include both cell values needing to be met. Newbie to formulas. Thank you.

I Can make conditional formatting for individual cell for example D8 when it is zero make red Pattern and white font.

But on same condition (D8=0) I like to make all column D with red Pattern Background.
How is that possible?

See attached example

Thanks in advance

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 have a large multi-columned spreadsheet where I want to use conditional formatting. For simplicity let me say that one column has a status which may be C for Complete, U for Underway and H for Held. Based on whichever character is within that cell I wish to shade / colour the entire row. Please note that the relevant cell will be contained within the row that is to be shaded.

I think this can be done but my half hour search of the board did not yield an answer.


PS The reason I want the entire row shaded is for when I'm only printing certain columns that may not include the Status

This is an attempt to provide an attendance form for completion at a
meeting. Currently a hard copy is printed, the record is made by pen
or pencil, a mark to show who is present, and then I have to enter
the results into my spreadsheet.

Is it possible to apply conditional formatting to the cells so that a
click changes the content of the cell between two states?

One state being default "Absent", (by colour if needed), and if the
cell is clicked the member is shown as being present, (by showing a
different colour), another click returns the cell to the default

Hi All:

I was wondering if someone could help me with this. I am making a form in
excel. When a user of this form clicks on a check box, I would like a series
of excel cells to change color. However, it seems to me that excel only lets
you apply conditional formatting for a cell if the condition is based on that
cell's value. I would like to control the formatting of the cell based on
another cell's value. Does anyone know how to do this?

THanks in advance for your help,



I am trying to setup conditional formatting for some adjacent cells in my
worksheet. For example is it possible to apply conditional formatting to
cell B2 when cell A2 meets the criteria you are looking for?

If the cell value is equal to today's date, then it should appear red as part
of the conditional formatting.

If D3=today(), then red/bold.

For insurance policies that will expire in the future, I would like a
formula to let me know when that date has arrived.

The cell date is that expiration date, so if I change that date to test my
formula, I find that it did not work.

What would be the best way to assign the formula?

Thanks in advance for your help.

cannot access conditional formating for a cell, why?

Is there a way to use conditional formatting to flag that something has been typed in a cell that should be calculated? I have a spreadsheet I need to share but with people that do not understand where and where they should NOT enter data at times. I'm trying to dummy-proof the spreadsheet a bit.

For example: if cell A3 reads "=A1+A2", then that cell is meant to sum these values and should not be used to enter data. I've tried locking cells and using the Protect Worksheet function but this creates other issues that I won't get into.

What I want to do is us conditional formatting to flag that cell with a RED fill as bold statement that you've done something you shouldn't do. Is this possible?

Thanks in advance,

Lets say based on a value in A5 I would like to change the text format for
cells d5:g5 (four columns), what would be the best procedure to do this, can
it be done using conditional format if each cell d5,e5,f5,g5 or is there
another way. By that do I input a conditional format for each cell
d5,e5,f5,g5 (if A5> "xx" then <format>.

A big Thank You for all the help.


I have to charts that I am trying to compare. The top one is blank and
I will enter in hours people have worked on a project. The bottom one
has the hours allocated to that person for that particular phase. They
are 5 columns by 12 rows.

I set a conditional format in place to say, if the number I enter in
the top sheet is larger than the corresponding number in bottom sheet,
make it red (so that I can know people are charging more hours than
they are allocated.

I don't know how to set conditional formatting for this large range of
cells, short of setting them up, one at a time.

Any help would be appreciated-


Excel is a tremendously powerful application. Why the miserly, or at least
seemingly arbitrary, restriction to three (magic number?) conditional formats
for a given cell or series of cells?

I am a university lecturer/administrator at the Université de Nancy 2 in
France and use Excel amongst other things to record details for incoming
admissions candidates for a vocational English course. The admissions
procedure includes a test whose result determines whether candidates are
refused, or allowed into various different level groups. I would like the
column which shows the decision to automatically display "refused" in red,
"abandon" in red italics, "accepted advanced level" in blue, "accepted
intermediate level" in green, and so on. Why can I only program three
conditions and not more (or can I, and I just haven't figured out how to?).

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

Hi, I am using excel 2003. I need to conditional formatting for the following:

Based on the column A, if it is USD, the next column show $ and the cell in blue
If it is JPY, the next column show Yuan sign and the cell in green
If it is Euro, the next column show Euro Sign and the cell in yellow.

How to make that happen?

Thanks a lot!

Hi my problem is that i want to set a same conditional formatting for more than 50 cells but it allows me to set one at a time. How to set conditional formatting for all the cells at a time ??


trying to figure out how to apply a Conditional Format (or another way of highlighting) for cells, that contain the same word, but in different phrases - e.g. "Multitarget" and "Fixed multitarget".

Can this be done?

(The normal Cond. formatting formula I found for similar cells is like this: "=IF(COUNTIF(E4:M4, E4)>1,TRUE,FALSE)", but obviously does not work with the above in mind ).


I have set up some rules under Format->Conditional formatting for a column of cells. One of the rule I am trying to set is that "If the cell is blank(or has null/missing value) then highlight it with Red color". I did setup the rule but it is not working.

Any ideas/suggestions?

Hi all,

I am a novice with excel 2010 but need some help with conditional formatting and particularly copy & pasting formulas. Sorry about the per-amble below but it might just make it clearer what I am trying to do. So here goes:

I am trying to put together a training matrix for a construction site so that when I enter a date on a certain cell, another cell changes colour to suit specified date ranges. I doing this by subtracting one date from the other and converting it to a number. This is repeated numerous times for each This number is kept in a particular cell - call the cell P8. There is a range of rows with the identical formula. These other rows relate to other training that may of been taken. So there may be differntt value in, for example, P9, P10, P11, etc. This bit is complete.

Next I have Conditionally Formatted one cell - call the cell D8. The problem I have is when I try to copy & paste this cell to the next D9 it keeps the same formula rather than changing the formula to follow on or be a bit clearer,

cel($D$8) shows on the conditioanl formatting $P$8<1 then turn red. When I go to paste this into $D$9 the same formula appears i.e. in $D$9 $P$8<1 then turn red.

I want this instead to be in $D$9 $P$9<1 and the cell to turn red & so on, & so on.

There will be quite a lot of repetition like this & the only way, so far, I have been able to do this is by manual adjusting each cell.

Any help is greatly appreciated

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