Color locked cells only in specified range
 

In spite of someones efforts to help me, I am not able to modify or ?? the
following to work on locked cells (only) in the selected range in column C.
It is critical to the application. Will someone please help?

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

Color locked cells only in specified range
 

I assume the worksheet is unprotected when you run the code


Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim ILastRow As Long, cell as Range

ILastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow


For each cell in ws.Range("C6:C" & ILastRow)
With cell
.Select
if .Locked then
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=($B" & _
cell.row & =""*"")"
.FormatConditions(1).Interior.ColorIndex = 4
end if
End With
Next
ws.Range("C6").Select

--
Regards,
Tom Ogilvy

"BEEJAY" wrote:

In spite of someones efforts to help me, I am not able to modify or ?? the
following to work on locked cells (only) in the selected range in column C.
It is critical to the application. Will someone please help?

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

Color locked cells only in specified range
 

Tom:
Thanks so much for the impressively prompt response.
I'm getting a an error: Compile Error: Syntax error on the following:

..FormatConditions.Add Type:=xlExpression, Formula1:="=($B" & _
cell.row & = ""*"")"
As I was inputting the code, it kept on going back to the last = sign, and
indicating
Compile Error, Expected Expression.

Help Pls. (again)

"Tom Ogilvy" wrote:

I assume the worksheet is unprotected when you run the code


Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim ILastRow As Long, cell as Range

ILastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow


For each cell in ws.Range("C6:C" & ILastRow)
With cell
.Select
if .Locked then
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=($B" & _
cell.row & =""*"")"
.FormatConditions(1).Interior.ColorIndex = 4
end if
End With
Next
ws.Range("C6").Select

--
Regards,
Tom Ogilvy

"BEEJAY" wrote:

In spite of someones efforts to help me, I am not able to modify or ?? the
following to work on locked cells (only) in the selected range in column C.
It is critical to the application. Will someone please help?

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

Color locked cells only in specified range
 

Looks like I had a typo. should have been cell.row & "= ""*"")"

I ran this successfully

Sub AARR()

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

ILastRow = ws.Range("B:B") _
.SpecialCells(xlCellTypeLastCell).Row


For Each cell In ws.Range("C6:C" & ILastRow)
With cell
.Select
If .Locked Then
.FormatConditions.Delete
.FormatConditions.Add Type:= _
xlExpression, Formula1:="=($B" & _
cell.Row & "=""*"")"
.FormatConditions(1).Interior.ColorIndex = 4
End If
End With
Next
ws.Range("C6").Select


End Sub

It performed as I expected, but I can't say that is what you expect.
--
Regards,
Tom Ogilvy


"BEEJAY" wrote:

Tom:
Thanks so much for the impressively prompt response.
I'm getting a an error: Compile Error: Syntax error on the following:

.FormatConditions.Add Type:=xlExpression, Formula1:="=($B" & _
cell.row & = ""*"")"
As I was inputting the code, it kept on going back to the last = sign, and
indicating
Compile Error, Expected Expression.

Help Pls. (again)

"Tom Ogilvy" wrote:

I assume the worksheet is unprotected when you run the code


Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim ILastRow As Long, cell as Range

ILastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow


For each cell in ws.Range("C6:C" & ILastRow)
With cell
.Select
if .Locked then
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=($B" & _
cell.row & =""*"")"
.FormatConditions(1).Interior.ColorIndex = 4
end if
End With
Next
ws.Range("C6").Select

--
Regards,
Tom Ogilvy

"BEEJAY" wrote:

In spite of someones efforts to help me, I am not able to modify or ?? the
following to work on locked cells (only) in the selected range in column C.
It is critical to the application. Will someone please help?

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

Color locked cells only in specified range
 

Tom:
It also works as I expected.
Thanks much.
JFS

"Tom Ogilvy" wrote:

Looks like I had a typo. should have been cell.row & "= ""*"")"

I ran this successfully

Sub AARR()

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

ILastRow = ws.Range("B:B") _
.SpecialCells(xlCellTypeLastCell).Row


For Each cell In ws.Range("C6:C" & ILastRow)
With cell
.Select
If .Locked Then
.FormatConditions.Delete
.FormatConditions.Add Type:= _
xlExpression, Formula1:="=($B" & _
cell.Row & "=""*"")"
.FormatConditions(1).Interior.ColorIndex = 4
End If
End With
Next
ws.Range("C6").Select


End Sub

It performed as I expected, but I can't say that is what you expect.
--
Regards,
Tom Ogilvy


"BEEJAY" wrote:

Tom:
Thanks so much for the impressively prompt response.
I'm getting a an error: Compile Error: Syntax error on the following:

.FormatConditions.Add Type:=xlExpression, Formula1:="=($B" & _
cell.row & = ""*"")"
As I was inputting the code, it kept on going back to the last = sign, and
indicating
Compile Error, Expected Expression.

Help Pls. (again)

"Tom Ogilvy" wrote:

I assume the worksheet is unprotected when you run the code


Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim ILastRow As Long, cell as Range

ILastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow


For each cell in ws.Range("C6:C" & ILastRow)
With cell
.Select
if .Locked then
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=($B" & _
cell.row & =""*"")"
.FormatConditions(1).Interior.ColorIndex = 4
end if
End With
Next
ws.Range("C6").Select

--
Regards,
Tom Ogilvy

"BEEJAY" wrote:

In spite of someones efforts to help me, I am not able to modify or ?? the
following to work on locked cells (only) in the selected range in column C.
It is critical to the application. Will someone please help?

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