Excel 2003; SP2
Got the following from this NG yesterday. Works great.
While testing determined one extra step needed.
Column C has some yellow cells in it, indicating unlocked cells.
(In this application: Unlocked is alway yellow. Yellow always means
unlocked.
It indicates to the users what cells they can manipulate.)

They are NOT to be over-colored by the green.
I call it over-coloured since if the criterial is changed so that the green
is removed, those specifc cells "revert back" to yellow.

Is there a way to "by-pass" the unlocked (yellow) cells in the following?

Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim ILastRow As Long

ILastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow
With ws.Range("C6:C" & ILastRow)
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=($B6=""*"")"
.FormatConditions(1).Interior.ColorIndex = 4
End With
ws.Range("C6").Select

End Sub

maybe something like

dim c as range

for each c in ws.Range("C6:C" & ILastRow).cells
if not c.locked then
'add validation
end if
next c




--
Tim Williams
Palo Alto, CA


"BEEJAY" wrote in message ...
Excel 2003; SP2
Got the following from this NG yesterday. Works great.
While testing determined one extra step needed.
Column C has some yellow cells in it, indicating unlocked cells.
(In this application: Unlocked is alway yellow. Yellow always means
unlocked.
It indicates to the users what cells they can manipulate.)

They are NOT to be over-colored by the green.
I call it over-coloured since if the criterial is changed so that the green
is removed, those specifc cells "revert back" to yellow.

Is there a way to "by-pass" the unlocked (yellow) cells in the following?

Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim ILastRow As Long

ILastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow
With ws.Range("C6:C" & ILastRow)
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=($B6=""*"")"
.FormatConditions(1).Interior.ColorIndex = 4
End With
ws.Range("C6").Select

End Sub

Tim:
Thanks for the response.
I've played around with this for quite some time and cannot get things to
work.
In the meantime, I've started to wonder if it would make more sense to do
the opposite namely format only the locked cells.
Would this be any easier to code?

"Tim Williams" wrote:

maybe something like

dim c as range

for each c in ws.Range("C6:C" & ILastRow).cells
if not c.locked then
'add validation
end if
next c




--
Tim Williams
Palo Alto, CA


"BEEJAY" wrote in message ...
Excel 2003; SP2
Got the following from this NG yesterday. Works great.
While testing determined one extra step needed.
Column C has some yellow cells in it, indicating unlocked cells.
(In this application: Unlocked is alway yellow. Yellow always means
unlocked.
It indicates to the users what cells they can manipulate.)

They are NOT to be over-colored by the green.
I call it over-coloured since if the criterial is changed so that the green
is removed, those specifc cells "revert back" to yellow.

Is there a way to "by-pass" the unlocked (yellow) cells in the following?

Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim ILastRow As Long

ILastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow
With ws.Range("C6:C" & ILastRow)
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=($B6=""*"")"
.FormatConditions(1).Interior.ColorIndex = 4
End With
ws.Range("C6").Select

End Sub