Color locked cells only in specified range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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).Row
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
 
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).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
 
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)
 
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.
 
Back
Top