Well, here are a number of disconnected comments. I don't expect they're exhaustive; there's probably more to talk about. But they're what I see off the top of my head:
1) You say it applied the conditional formatting to the whole spreadsheet. I can tell that from here: You applied it to Selection, and you set the selection to Cells.
Here's one of those fundamentals I want you to understand: When you name an object, if you don't name its parent you subject yourself to Excel's notions of defaults. As long as you know what the defaults are, that's ok; you're doing it with your eyes open.
Cells, for example, means all the cells in a particular worksheet. (That is, Cells by itself means that. I usually specify a particular cell by referring to, eg, Cells(2, 5); that means the cell in row 2, column 5, ie E2.) But which worksheet? I believe the default is ActiveWorksheet, though someone else here may contradict me. I say "I believe" because in practice I almost always specify.
ActiveWorksheet, fine—but of which workbook? Again, I believe it's ActiveWorkbook, though it may be ThisWorkbook.
So in my own programs I almost always start out like this:
Code:
Set owb = ThisWorkbook
Set owss = owb.Worksheets
Set owsFrom = owss("Worksheet name #1")
Set owsTo = owss("Worksheet name #2")
...and so on, making sure that from then on instead of saying Cells I can say owsFrom.Cells or owsTo.Cells and not worry about whether I've munged up something. For example, if I get in the habit of saying just Cells, and then I do something that changes the active worksheet, my program will happily go on doing its thing to the new worksheet, which may make me very unhappy...or, worse, I may not notice until later.
Anyway, back to the subject: You said "Cells.Select", which set the selection to all the cells in the active worksheet, and then started working with Selection.FormatConditions. So naturally what you did applied to the whole worksheet. If you wanted to set the formatting for just A1:L<whatever>, then set rz before you do the formatting:
Code:
rz = Range("L1").End(xlDown).Row
Range("A1:L" & rz).Select
Selection.EntireColumn.Autofit
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B2=1"
...and so on.
By the way, I wouldn't use Select, either; I'd set a variable. That way I can't accidentally change the Selection and have it change the way my program works. Like this:
Code:
rz = Range("L1").End(xlDown).Row
Set org = Range("A1:L" & rz)
org.EntireColumn.Autofit
org.FormatConditions.Add Type:=xlExpression, Formula1:="=$B2=1"
...and so on.
2) I don't think the problem is that you selected the whole worksheet for your conditional formatting. Oh, it was a mistake, but only in the sense that you didn't mean to do it; the conditional format should still have worked. Must be something else. You say it worked for only some rows, so let's take a look at the statements you used. First:
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B2=1"
I'm not sure how this should work, because I never use A1 notation. I would have used R1C1 notation, and the same statement might have looked like this:
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=RC2=1"
R1C1 notation expresses cells in the form "R
nC
n" where the two numbers are row and column respectively. So "RC2" means "this row, column 2"; whereas "$B2" means "second column, and the row that is 2 in relation to
this row" (whatever this row is at the moment). I don't know how that works in conditional formatting.
Code:
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
The first object is Selection, and of course you know what that is. FormatConditions is a collection of FormatCondition objects belonging to the current Selection. ("A collection" means there may be more than one of them.) When you refer to FormatConditions(
n), you're saying you want the
nth one in the collection; when you specify FormatConditions.Count, that means the number of items in the collection, ie the last one. So whatever SetFirstPriority is, you're doing it to the last conditional format in the Selection, ie in the worksheet.
But then you go on to work with the
first conditional format:
Code:
With Selection.FormatConditions(1).Font
.
.
.
Selection.FormatConditions(1).StopIfTrue = False
If there's only one FormatCondition object in the whole sheet then 1 and FormatConditions.Count are the same. But I don't know that it's true. That may be part of your problem. It's
definitely a problem in the sense that assuming they're the same will leap up and bite you unexpectedly one day; whether it's already done so I don't know yet.
To investigate why your conditional formatting wasn't applied to all the relevant rows, I suggest modifying your code match your new understanding...whatever new understanding I've managed to convey to you :-)...and then, if it still doesn't work, I'll need to know exactly which rows didn't work. I'll probably need to see the worksheet itself, to be sure; can you upload it, or are there classification issues?