Free Microsoft Excel 2013 Quick Reference

Applying conditional formatting to multiple cells in a Column

For the life of me I haven't been able to figure out a proper way to do this besides manually updating each cell. This seems like such a simple task but hard to figure out. After 1 year of manually doing it I decided to ask for help and see if there is a faster way to do this because I will be doing this for more years to come lol. So here is my test excel sheet:

A1 Value = "167"
B1 Value = "0"

I click on B2 and click on Conditional Formatting and set it up so that [IF B1 value =A1 value then B1 bgcolour is Green]. Simple right? Yes.

Heres, where it gets tricky for me...

How do I copy and paste this conditional formatting so that I can continue this pattern so that [IF B2 value = A2 then bgcolour is Green] ?

If you tell me to click on the fill button and drag down, it won't work because Excel will screw up the conditional formatting so that
[IF B2 value is A1 then bgcolour is Green]
[IF B3 value is A1 then bgcolour is Green]
[IF B4 value is A1 then bgcolour is Green]
[IF B5 value is A1 then bgcolour is Green]

The conditional formatting does not want to increase A1 to A2, A3, A4, A5 etc.

What is the best way to copy and paste my conditional formatting formula so that the conditional formatted cell checks the cell to its left to verify data?

I have included a visual example of an attached XLS file to make it easier for you to understand and try out yourself.
Thank you!

Post your answer or comment

comments powered by Disqus
I am looking for a way to apply conditional formatting to cells that are
blank in a spreadsheet, but I don't know how to represent a blank cell - I
have tried " " and "" with no luck. I am applying two conditions to an
entire column - one that says if the value is not equal to "Yes" (there can
be any number of alternate values in the cell) - then fill with red, I want
the second condition to be that if the cell is blank then no fill. The end
result I am looking for is that values of Yes and blank cells will not have
the conditional formatting applied - all others will be colored red. Maybe
I'm going about it backward.

Can this be done?

Thanks for any insights,

How do you simultaneously apply conditional formatting to multiple worksheets
in Excel 2007?

In 2003, you could select mutliple worksheet tabs, apply conditional
formatting to one worksheet, and it would populate to the other worksheets
simultaneously. This function is not available in Excel 2007. How can this
be done in Excel 2007?


Until now, I have copied a formula to all the relevant cells in a column by
clicking on the bottom right corner and dragging down through the rows.

What I need now is to apply the formula to ALL cells in a column. I don't
want to have to hold and drag down through 65000 rows.

Is there any menu option I can use to write the formula I need in cell A1,
then have it automatically apply to cells A2 through A65000 (or whatever the
end row value is).

Thanks for any help.


I have three columns in an excel sheet, the first column has a dollar value, the second is the tax on that dollar value and the third is the vendor to which the dollar amount was paid to. Only certain vendors, however are tax exempt. What I would like to do is enter the dollar value, select my vendor and have the tax calculated. On a separate sheet, I've set up a vendors list along with their tax exemption status, yes or no. This allows me to have a pull down list of vendors using the data validation method to select my vendor. The dollar amount is entered manually and for the tax calculation I use the following formula
=IF(VLOOKUP(C3, Sheet2!A3:B5, 2, FALSE) = "yes", A3*0.05, " ")
The C column is the vendor entry
Sheet2 is the vendor list
The A column is the dollar amount entry

For this particular row, it takes the vendor(C3), locates it in the A column of my vendor list (Sheet2!) and returns a yes or no from the B column. If it's "yes" then it calculates a 5% tax on the dollar amount(A3), if not it leaves it blank.

For the next row, the formula should look like this
=IF(VLOOKUP(C4, Sheet2!A3:B5, 2, FALSE) = "yes", A4*0.05, " ")
only the vendor(C4) and dollar amount(A4) change not the lookup table.

However, if I click on the bottom right corner of the first cell in the tax column and extend it to the next cell down so as to copy the formula it ends up with this
=IF(VLOOKUP(C4, Sheet2!A4:B6, 2, FALSE) = "yes", A4*0.05, " ")
You will notice that the lookup tables parameters have changed from A3:B5 to A4:B6.

How can I apply the same formula to all cells in a column and control which variables are affected?


I want to create a conditional format which will change the color of a cell
depending on the user's choice. The cells which I am referencing are
scattered througout the worksheet (i.e. E84, E88, E90, etc.) How can I set
this conditional format to multiple cells? Or is there a way to set the
conditional format to text within a cell? any suggestions would be greatly

Thank you,

I have used "filter" to see only certain cells in a column. When I attempt to use format painter to format the visable cells only it also formats the filtered cells.

How do I format only the visable cells?

Thanks for your help

Hi all,

My first time in using Excel with calculations. Problem is as follows:

I have three rows: First: beginning date, Second: end date, Third: time difference.

third is calculated by obviously subtracting second from first. My problem is, I want to apply this formula to all cells in third column. i.e. :

c3 = b3 - a3

c4 = b4- a4

c5 = b5 - a5


cn = bn - an

I need something like a for loop. How do I do that without manually writing the formula seperately to all cells in my third column?

Thanks in advance..

Hey everyone

I have been looking everywhere on how to combine multiple cells in a column so they end up in one cell on top of one another. The script or command would also need to know when there is a space in one of the cells and stop, then start all over once the next set of data is found. The amount of cells in the column will vary.


Cell A1: data1
Cell A2: data2
Cell A3: data3

Cell A4: data4

Cell A5: data5
Cell A6: data6





Any assistance would be appreciated, thanks.

Is there a formula to use to add a quantity to every cell in a column?

I need to be able to add a quantity of 3 to every row in a column.

Any help would be appreciated.


Hi All,

I am trying to figure out how to add a fixed amount in percent (ie. 10%) to all cells in a column.
Here is an example:


I want to add 10% to everything in the above column to give:


The column has thousands of items so manual editing is not something I can complete this year ;-)

If this involves some kind of formula would this prevent me from exporting as a CSV file - or would the CSV simply contain the numbers rather than the underlying formula?

Apologies if this is a simple question but I am stumped :-?

Any help appreciated!



How do I quickly add a fixed value to every cell in a column?



I am trying to create a conditional format using VBA and apply that format to several rows in 2 columns. When I walk through the code the formula string contains the correct cell reference, however when the macro completes and I review the conditional format for the cells in the sheet the cell reference starts 9 rows and 1 column past what I want. I want the formula in cell A10 to be "=LEN(A10)>0" but when its executed I get "=LEN(A19)>0"
Below is the code I've created. (Note: wrkSht is a worksheet variable initialized to the correct sheet. This code is in a method inside of a class module)

firstRow = 10 
firstCol = 1 
lastRow = 500 
lastCol = 3 
Set rng = wrkSht.Range(wrkSht.Cells(firstRow, firstCol), wrkSht.Cells(lastRow, lastCol)) 
strFormula = "=LEN(A10)>0" 
rng.FormatConditions.Add Type:=xlExpression, Formula1:=strFormula 
rng.FormatConditions(1).Interior.ColorIndex = 24 
Set rng = Nothing 

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

I applied a conditional format for one cell.
The condition is this,
If the (1-Jan-07) Google ranking No. is greater than (1-Dec-06) the number is displayed in red & Bold
If the (1-Jan-07) Google ranking No. is lesser than (1-Dec-06) the number is displayed in green & Bold

Actually I applied the condition for one cell, How to apply this condition to mutiple cells/other cells using drag & drop or one shot.
If i select the conditional cell and drag to apply to other cells, the condition is applying but, the corresponding cell no. is not changing

Please Help!

Kind Attenation Forum Administor: I tried to attach a Excel file, but it is saying invalid file.


I'm trying to add conditional formatting to my worksheet using a macro.
The condition is:
Look at all cells in Column B
If the cell value equals "Capacity" then change the background color of that cell and the rest of the cells in that row (e.g. from Column B to O).
E.G. if cell B6 = "Capacity" then change the background color of cells B6 to O6 to light blue
If cell B7 = "Availability" then change the background color of cells B7 to O7 to red

This should apply to all cell in column B and change the background color of appropriate cells.

The code I have so far is

    Dim rngToFormat As Range 
    Set rngToFormat = ActiveSheet.Range("B6:B100") 
     'first, clear any old Cond format for these cells
     'add cond 1: if = "Capacity"
    rngToFormat.FormatConditions.Add Type:=xlCellValue, _ 
    Operator:=xlEqual, Formula1:="Capacity" 
    rngToFormat.FormatConditions(1).Interior.ColorIndex = 34 
     'add cond 2: if = "Availability"
    rngToFormat.FormatConditions.Add Type:=xlCellValue, _ 
    Operator:=xlEqual, Formula1:="Availability" 
    rngToFormat.FormatConditions(2).Interior.ColorIndex = 3 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works to a certain extent but only changes the background color of the cell in column B, but should change Column B to O

See attachment
Can anyone help please


Wow, I just be brain dead. In Excel 2003, I could choose individual
conditional formatting for one cell. In Excel 2007, it seems I have to choose
rules for the entire sheet....I don't want to do that. I just want to apply
a conditional format to one individual cell. How can I accomplish that?

Thank you all for your help!

Hi, I'm new here.

I have a college asignment and I need to apply conditional formatting to show if a value has increase by more than 3%. I have cells similar to the following:

30 27
98 95
48 47
40 35

I need to apply conditional formatting to the cells in E column, if they have increased by more than 3% to that of the values in the F column.

Any suggested solution would be greatly appreciated.

Hi, I am looking for a way to total the amount of cells in a column/range that contain red text. Is this possible please?

How can I add the number "10" to each existing number in a column. More specifically, I have 450 cells in a column that contain different ID numbers. I need to add "10" to the beginning of each of these numbers and any future ID numbers in the column. Is there a simple function to achieve this?

I want to use conditional formatting to highlight cells in a column that do not have formulas in them. The problem is that I do not know of a formula that can test if a certain cell contains a formula or not. If there is such a formula out there can someone please share it with me.

Hi! I'm new to this and have been wrestling with the problem of trying to add a drop down list to each cell in a column. More specifically, I have a spreadsheet of business expenses with column headers such as: date, mileage, case #, description, etc. I want to add a column of Tax Catagories, with each cell below it having a dropdown list; and with a mouse click on the correct catagory, the cell will fill with the text. The list should contain: Advertising, Car Expenses, Office Expense, Travel, Meals and Entertainment, Rent, Supplies....well, you get the idea...the IRS Schedule C business form.

I typed the list on worksheet 2 and named it "TaxCatagory" in the box next to the function sign. I then went to worksheet 1 (where my spreadsheet is), clicked on the header cell of a blank column, went to DATA > VALADATE > LIST > =TaxCatagory, and enter. NADA. I then tried highlighting the whole column of cells and repeating the process. NADA again. I get a warning sign, but I am not savy enough with the terminology to know to what it refers. Help would be great!

Sorry for being such a neophyte!


I am trying to write a code to merge cells in a column based on the cells highlighted in another column and then add data to it. I use the .merge function but gets all messy.

Below is an example of what i am trying to achieve.

Yellow 5
Green 2
Red 3

Any help would be highly appreciated.
thanks in advance.

Hello all,

I have a dataset that needs to have the following macro/Conditional Formatting applied to each subsequent row:

Sub CondFrmt()
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=$B$9"
Selection.FormatConditions(1).Interior.ColorIndex = 40
End Sub

Basically, I want a cell in a row to be filled if it is 'equal to or greater than' the cell value in Column B of the same row. This Column is subject to being changed by another sheet and the way I have been doing it before was via the menus and the formatting remains correct when the data in Column B is changed, but the row number is due to increase, so want an automatic way to achieve this!

My problem is, I can't figure out how to get it to apply this to each row (until there are no more rows). Putting a loop in would also increase the time, which may be a problem in the future (but may suffice for now)...

It should be really obvious and I'm sure I've done something like this before, but I'm suffering from a mental block - probably because it's Friday!

I appreciate any help that can be provided.

Is this even possible? I've done conditional formatting to a cell based on
that same cell's info BUT what I want to do now is something like this...Can
I apply conditional formatting to cell A3 based on the information in cell
B3? If so, how do I do that?? I completely suck at writing formulas so, if
you know the answer, can you help me with how to write the formula, too?
Basically, I want to apply formatting to A3 if B3 has a value greater than
zero. Thank you!!

Dear Excel-ers,

I am new to this forum and completely new to Excel macros so I hope someone can advise me!

I have a worksheet consisting of 85 columns of reaction time data, each column representing a variable or condition.

I wish to highlight outliers - in this case, those that are more than 2.5 standard deviations away from the column mean.

So, under each column I've calculated the mean, standard deviation, and standard deviation x 2.5.

To highlight outliers in the first column I did the following:

1) selected the range of cases (a2 - a40)
2) applied conditional formatting (red type) if values were greater than the mean + sd*2.5 or less than the mean - sd*2.5 (the mean and sd*2.5 values are taken from the previously calculated values at the bottom of each column.

Which did the trick. However, as I have 85 columns and then over 30 workbooks with an identical format, it would be great to automate this process.

I've never used macros before in Excel, but I tried recording a macro while completing the above actions for a few columns:

    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
    Selection.FormatConditions(2).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
    Selection.FormatConditions(2).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
    Selection.FormatConditions(2).Font.ColorIndex = 3
End Sub
So, my question is, other than manually recording a macro and repeating this action for the entire 85 columns, or manually editing the code to apply to the different columns, is there a simpler way to achieve this? The range of cells I want to highlight is the same in each column. Also the position of the mean, sd and sd*2 is the same in each column. I rather suspect that they wouldn't be necessary at all to someone with a modicum of macro experience.

Any suggestions would be greatly appreciated. Many thanks for reading and do let me know if any further information is required.

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