conditional formattingmicrosoft excel
I want to apply conditional formatting to change the background color of alternating rows on a spread sheet. The rows will alternate between two colors (yellow and green) two lines each.
This is the code I am using:
=MOD(ROW(),2)*($A1<>"")
and
=(1-MOD(ROW(),2))*($A1<>"")
It changes every other line, but I want every two lines to be the same color. What do I need to change to make that work?
Here's a macro that creates a conditional format for each row in your selection. It does this by copying the format of the first row to EACH row in the selection (one by one, not altogether). Replace B1:P1 with the reference to the first row in your data table.
Sub NewCF()
Range("B1:P1").Copy
For Each r In Selection.Rows
r.PasteSpecial (xlPasteFormats)
Next r
Application.CutCopyMode = False
End Sub
To use, highlight the un-formatted rows in your dataset (in my case, B2:P300) and then run the macro. In the example below, note that the max numbers in the first two rows are 5 and 15, respectively; both cells are dark red.
I'm sure there's a faster solution than this, though.
The problem using DATEDIF is it doesn't calculate negatives. Microsoft states:
Displays a #NUM! error because start_date occurs before end_date (#NUM!)
Therefore, the rule doesn't run as you entered it. Since Excel recognizes dates, use the simple function of SUM on this and it works as you described.
=SUM($N$9-N12)>=3 for green
=SUM($N$9-N12)=2 for yellow
=SUM($N$9-N12)<2 for red
Here are the rules.
And the results.
NOTE: My date format is m/d/yyy. This was done in 2010, but should be the same to 2013.
Best Answer
to save some processor cycles, you can format whole range with the 1st color and use this formula to conditionally format 3rd and 4th row of every 4 rows:
if you use this conditional format only to used range, e.g. by creating an Excel Table (the one without alternating row colors), there should not be need to include
$A1<>"", but feel free to use it anyway