# Easily Apply Conditional Format Formula To All Rows

Aug 25, 2009

i have the following formula applied to a conditional format, on row 12:

cell value > less than =(\$K12:Y\$111)*(1-0.05)

i want to use this on all rows, however i have 300+ rows.

Is there a way to apply this to all rows between column K and Y, i don't like the thought of creating 300+ conditional formats!

## Apply Conditional Format On Several Rows?

Aug 12, 2012

I have a conditonal format of cells across Row 2 based on the contents of cell D2. The formula for the conditional format is

=\$D\$2="NONE"

The formatting applies to these cells:

=\$F\$2:\$G\$2,\$I\$2:\$K\$2,\$M\$2:\$N\$2,\$O\$2,\$T\$2:\$V\$2,\$X\$2:\$AA\$2

I would like to fill in the same conditional format down to 51 other rows but the formatting for each row should be based on the contents of its own cell i.e. \$D\$3, \$D\$4, \$D\$5 etc. How can I do this with code?

## Only Apply Conditional Format If Cell Val > 0

May 10, 2013

I have a column in a sheet referencing data from a column in another sheet in my workbook. I want to apply conditional formatting on these numeric values, from red for lowest vals to green for highest. However the fact that my column is referencing cells that will not yet have data means that they are being pulled over as 0. I am not displaying zero values in cells but the zero vals are still being included in the conditional format. Is there any way to discount these zero vals?

## How To Apply Conditional Format To A Range With VBA

Feb 27, 2012

I recorded a macro in which I applied a conditional format to a range (the selection).But, when i replay this macro, the conditional format is only applied to the first cell in the range. This can be seen in the "Manage Rules" dialog of Conditional formatting, where the "applies to" column shows only one cell, even though a range of cells was selected by the code.

I set a breakpoint at the first line of code here, and confirmed that the selection is correct, and indeed it is, as in the immediate window, ?Selection.Address correctly gives "\$O\$6:\$O\$21".

"=O6/(O6+P6)>25%"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlGray16

[code]....

why the conditional format ends up only applied to cell O6 after the code runs?

"How can i apply a conditional format to a range of multiple cells, based on an expression, using VBA?"

Note that my range is within a PivotTable (but still, this works fine in the UI when i record the macro and the conditional format is correctly applied to the entire selected range).

## Apply Conditional Format Only When Cells Are Active?

Mar 31, 2013

I was wondering if is possible to apply a conditional format rule to a range of cells only when certain other cells outside of the range are the active cells. eg row C4-J4 has a conditional format to identify duplicates from range C5-C10, but I also want the same conditional rule to apply separately and uniquely if the active cell falls within the next active range D5-D10. So basically only apply conditional formatting separately and uniquely as the active cell moves its way across the columns?

## Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?

Feb 9, 2009

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.

## Print Out Individual Rows Easily

May 16, 2012

I have created a spreadsheet to do all payroll calculations. Would like to print out each individual persons detail to give them a "receipt" of gross pay and deductions.

How I could take a sheet with 100 names and print out each line.

## Use VBA To Apply Formula Only To Used Rows

Aug 18, 2013

I'm using this to apply a formula to a worksheet:

Code:
Private Sub Worksheet_Activate()
Range("B2:B7000").Formula = "=IF(COUNTIF(\$C\$2:\$C\$7000,C2)=0,"""",COUNTIF(\$C\$2:\$C\$7000,C2))"
End Sub

Is it possible to amend this so that it only applies the formula to the used range? Although the current range goes up to 7000 this increases the file size so to keep it to a minimum I was wondering if there was a way to do it only to the used range.

## Easily Change Column Reference In Formula Argument

Jun 17, 2009

i have the table of holiday days with 7 columns(7 countries).Each column consists of 10(or more rows (holiday days).

I have a formula for calculating start day for a deposit which has to be renewed.
formula is =workdays(end date of deposit,-2,A1:A10).
-2 in formula is because all the trades in the market are traded Today+2days.
A1:A10 is column of holiday days.
This formula calculates the start of deposit excluding weekends,holiday days.

BUT I have more then 7 countries and I would like to have a macro so when in cell F10 I type SWEDEN,my formula automatically use the column with holiday days B1:B10 for sweden.
When I type Finland, the formula uses column C1:C10 with holiday days from Finland respectively.

## Macro To Apply Existing Formula To All Rows In A Sheet

Jan 26, 2009

I'm currently applying these formulae manually, and also instructing other people to do the same, due to my lack of understanding of macros. Hopefully someone can give me some instruction as to how to do this automatically. I'd like people to be able to open sheets sent as normal CSV's and apply a macro to get them into the right format, changing only rows with data in them.

These are the instructions I'm currently sending:

In cell n1 enter:
full phone number

in cell m2 enter:
=RIGHT("00000000" & J2,8)

Drag m2 to last record

In cell n2 enter:
=CONCATENATE(61,I2,M2)

Drag n2 to last record

In cell o1 enter:
Date of call

in cell o2 enter:
=DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))

## Conditional Format Whole Rows With More Than 3 Conditions

May 14, 2012

I am needing to format a spreadsheet using 2003 which only allows 3 conditional formats, but I have 4 conditions.

I need to highlight the row if column W has a

G - green (colorindex = 35)
R - red (colorindex = 3)
Y - yellow (colorindex = 36)
O - orange (colorindex = 44)

how I can do this?

## Deleting Rows Without Conditional Format?

Jul 3, 2013

I have a report with over 12,000 rows of info.

I have set conditional formatting to highlight yellow if the rows contain a certain word.

I need to delete all the non-highlighted rows.

## Conditional Format Multiple Rows?

Feb 19, 2014

Is it possible to alternate how a condition works over many rows? Say column C has work order numbers eg. C2:C7 shows WO1234, C8:C15 shows WO1235, C16:C25 shows WO1236 and C26:C30 shows WO1237. What I would like to do is have rows 2:7 red, rows 8:15 blue, row 16:25 red, rows 26:30 blue and repeat to end (the range would be column A to column N. Ive used =MOD(ROW(),2) for every second row but am not sure how to get it to look at many rows and alternate as needed.

## Conditional Format Over Range Of Rows

Feb 25, 2008

I've been trying to get around the 3 rule limit for conditional formatting,
and I've found the code I need on the site

[url]

I've changed it to suit my needs

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A1:A2000")) Is Nothing Then
Select Case Target
Case Is = "Test"
icolor = 6
Case Is = "Test2"
icolor = 12
Case Is = "Test3"
icolor = 7
Case Is = "Test4"
icolor = 53
Case Is = "Test5"
icolor = 15
Case Is = "Test6"
icolor = 42
Case Else
End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

This works great for one cell, but I need to format a row range based on the single cell.
So if Test is entered into A1, then it changes to yellow, I also need the next 6 cells on the row to change too- In this case [B1:G1].

But it needs to cover the range in the code A1:A2000

## Conditional Format - To Do All The Rows At Once Instead Of One At A Time

Nov 17, 2008

I have a list of 50 sites (in rows) and their revenue for the last 12 months (in columns). I would like to use conditional format to highlight the highest number in each row but I can only do this one row at a time using top/bottom rules (top 1%). Is there a formula that I can use to do all the rows at once instead of one at a time?

## Conditional Format Rows 4+ Criteria

Mar 7, 2007

What I am looking to do is format a row so that it is highlighted if one cell in that row meets one of two criteria or if another cell in that row meets one of two criteria: I was hoping to use the excel conditional formating tool but I'm stumped as I need 4 criteria and it can only do 3 criteria.

The cells in question require a greater than or less than criteria. If could use something like: Formular is =\$AJ16 <= -0.11 or =\$AJ16 >= 0.11 and
Formular is =\$AK16 <= -0.11 or =\$AK16 >= 0.11 I would be very happy but it won't let me do this - is there a way it can be done?

## Conditional Format Entire Rows In One Go

Feb 8, 2008

I've got a pivot table that shows different activities their cost, budget and percentage complete amongst other things. I would like to use conditional formatting to turn the font of a whole row grey if that activity is a 100% complete (exactly more then 99,5% complete in this case). I can do this with conditional formatting by selecting the individual rows and then setting the conditions. Example: selected row =\$45:\$45, Formatting condition =\$J\$45>99,5%.
However I've got 1500 rows to do....how can I set this condition for all rows in one go?

## Conditional Format Rows To Highlight Every Other Row With A Value Different Than Previous

Feb 11, 2014

do a conditional format rule that will highlight every other row of my excel spreadsheet when the value in a specific Column (say Column A) differs than the previous.

I've attached a sample worksheet with what I want it to look like after the conditional format rule is applied (every other row highlighted in light blue). The rule needs to apply to all rows in the worksheet beginning with ROW 2 (I don't need the rule to apply to the column header which is in ROW 1).

## Conditional Format Comparing Data Between Rows

Nov 11, 2009

(Core starting issue solved but other threads were created to continue with trouble spots.)

I'm terrified to use VBA just yet, so right now I'm determined to highlight values in my spreadsheet just using conditional formatting.

I have 5 plus 1 columns of data. For example,

A B C D E....F
3 2 4 5 7....2
4 6 1 3 4....1
4 7 4 5 8....4
2 1 5 3 9....1

I successfully applied a MIN formula to compare between columns on each
row and display in F the MIN value. Notice each cell going down F has the
minimum value in analyzing across each row.

Now, I *also* want to apply a formula on the whole F column to compare these resultant MIN values
and give me the MINIMUM value between those as well, so now I'm comparing across
rows. Got it? Now then.....I want the ENTIRE row that has this MIN value in F to be highlighted, because that will be the best choice for the application of what I'm doing. Did you understand this paragraph. It might have been confusing, but I think I said everything correctly, so read it carefully.

I've tried all sorts of "Formula is", "Cell value is", dollar sign this, dollar sign that, no dollar sign this, <, >, =, highlighted ALL the block of data, highlighting JUST the column across the rows of which will be compared, only highlighted one cell, but nothing has worked.

Is my problem that you can't have 2 formulas in one cell? So then I thought I should ADD another condition, but nothing works.

Let me throw in one more element that I did not state. If anywhere down the F column there is a 0, I don't want that to be my minimum. I want it to evaluate and give me the minimum value greater than 0.

Please read everything I wrote carefully. It might be confusing, but I hope someone has an answer, because it sure has frustrated me.

Now, I'm thinking that VBA programming may give me answers, but I am so terrified of macros, I don't know how to begin that. I have this impression based on the many sites I've looked at on the internet that many people do macros and it's quite easy, but it's funny to me that I am so scared. There's something about me freaking out that I won't have a period or a quote in the right place and I'll stress my day away over it.

## Conditional Formatting To Format Cells On Several Rows Below According To The Day In 2007

Sep 8, 2009

I´m having aproblem with Excel 2007 about Conditional Formatting. I have a row of Dates for example 02-01-2009 03-01-2009 04-01-2009 , etc in different columns.

Then what I want to do is use Conditional Formatting to Format cells on several rows below according to the day (if its weekend paint red, if not, dont do anything). I'm using the "Use a formula to determine which cells to format" and the condition is (supposing the cells with dates are A1 to C1)
=WEEKDAY(A1:C1) > 5 .

So with those 3 dates provided lets suppose Januar2nd is not a weekday, so the outputIwant is: Red White White, (Next Row) Red White White, etc for several rows.

Now what I think is not right is it only works for the row in which I have the cursor so its like: (Lets imagine I selected 3 rows on which I want to see that output) Red White White (The row in which the cursor is works fine), but the next row goes Red Red Red and the third the same.

## Conditional Format With Formula For Each Row

Oct 21, 2013

I have a file with two columns

Column A has a list of equipment names, for example: EQ1, EQ2, EQ3, etc.
Column B has serial numbers of equipment from column A.

I need to create a complex conditional formatting, which will check:

If A1=EQ1, and B1"HCC*" then make it red,
OR
if A1=EQ2, and B1"ABC*" then make it red,
OR
if A1=EQ3, and B1OR("CDE*","FGE") then make it red.

And then same thing for A2, B2 cells.

I have thousands of rows in both columns.

I have to check whether selected equipment name corresponds with entered serial name pattern. I don't know how to do it for each row separately.

I thought of something like:
1st condition: =AND(A1="EQ1",LEFT(B1,3)"HCC")
2nd condition: =AND(A1="EQ2",LEFT(B1,3)"ABC")
...

But how do I apply this conditional formatting to each row, so that it will compare A2 with B2, A3 with B3, and so on, instead of comparing A1 with B1 in all of the rows?

## Conditional Format If No Formula

May 29, 2009

I was wondering if anyone knows if you can set conditional formatting if the cell contains no formula but is not blank.

We use many formulas to calculate cells and we need to the cell to be colored if there is no formula entered. But the cell is not necessarily blank or zero.

## Conditional Format For Formula Or Number

Oct 2, 2009

I have a spreadsheet where I have certain cells that contain a link to copy a number from another tab in the spreadsheet.

At times, I have to plug in a number to try certain values. In doing this, I overwrite the link or formula. I then do an 'undo' to bring it back to the original link or formula.

I would like to put a conditional format based on the cell's content. If it is the formula, leave it as is. If it is overwritten by a number, I want it to change colors to remind me to undo my change when I am done.

The conditional formatting options I have found so far allow me to format according the the value in the cell but not the nature of it's content: number of formula.

## Conditional Format Using Formula That Contains VLookup

Mar 13, 2014

I have a set of data in A:P

I have a named range "TR" in which I need to look up a value

I would like to conditionally format Columns M:P if the value in the cell is less than a value in the lookup table

Column M corresponds with the 2nd column of the lookup table
Column N corresponds with the 3rd column of the lookup table ETC

My formula would be:

if M2 < Vlookup(b2&g2,TR,2,false) then conditional format
if N2 < Vlookup(b2&g2,TR,3,false) then conditional format

etc for Columns O and P

I have attached a sample workbook. TEST CONDITIONAL FORMAT.xlsx

## Combining Formula And Conditional Format

Dec 3, 2009

I would like to combine a formula with conditional format:

In P10
If I4 = "FPI/FPI" and P10 = "Y", then the cell pattern is a different color.

## Conditional Format Array Formula

Jun 19, 2014

How to conditionally format an array formula? The basic formula is an index(match criteria1 and match criteria2. I have come across sites where they mention you can incorporate aggregate into the formula to remove the Alt+Ctrl+Shift thus allowing for the formula to work in conditional formatting. Shown below is the formula I am trying to use. Basically if the formula below = today() then be yellow.

{=IFERROR(INDEX(Table2[Comp Req Date],MATCH(1,([@[SLDR_EF]]=
Table2[Component Material])*([@[Grand Sequence]]=Table2[Order Seq Num]),0)),"")}

## Conditional Format Formula Modification

Jun 17, 2009

I'm using this formula in a Conditional Format rule.

=CELL("row")=ROW()

Can anyone help me modify it, so that only the active cell is formatted instead of the entire row?

## Formula In Conditional Format Range

Jan 12, 2007

I want to gray out a row of cells (B11:M11) when "x" is entered in cell M11. Suggestions for using Conditional Formatting or a formula would be great. I have tried a couple of formulas but none work.

## Conditional Format Formula Based On Two Cells.

Apr 9, 2009

I want to create a conditional format formula which is based on values in 2 cells.

Example:

Cell B1 = 100
Cell C1 = 50

I want a formule for cell A1 which turns red for exmaple when B1 is higher then 85 and Cell C1 is lower then 85. Only in this case the conditional format should work.

## Cell Format For Conditional Formatting With A Formula?

Jan 25, 2012

I have a large spreadsheet with a number of columns that are set up for conditional formatting. For example a column of "BUY"'s and "SELL"'s where the "BUY"s are formatted with the default "light green fill, dark green text". I have other columns that are conditionally formatted using a formula. When I base the result on a formula I don't seem to have the option to use the default formats. Is it possible to use the defaults or maybe even how to customize my format to look like the default? It would look a lot better if I had a uniform red/green style throughout the sheet.