Merge consecutive cells using conditional formatting.
 

Here is what I was looking for

Suppose Cell B1= 2 , then I wanted to cells C1 and D1 to be blank and
locked ( Locked in the sence, no one should be able to write in those
cell. They should be as good as merged cell where I can't even blink my
cursor)

So depending on the values of the cell in column B, I wanted to make
the equivalenmt cells in that row as dead locked.
Suppose B1 = 3 then C1 D1 and E1 should be dead locked.
Suppose B2 = 4 then C2, D2, E2 and F2 should be dead locked.

Dead locked means no one should be able to write in nor format those
cell.

Merge consecutive cells using conditional formatting.
 

Sub SetCells()
ActiveSheet.Unprotect Password:="ABC"
Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Resize(1, cell.Value).Locked = True
End If
Next
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.Protect Password:="ABC"
End Sub

the default state for cells is locked, so if you want all other cells
selectable

Sub SetCells()
ActiveSheet.Unprotect Password:="ABC"
Cells.Locked = false
Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Resize(1, cell.Value).Locked = True
End If
Next
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.Protect Password:="ABC"
End Sub

--
Regards,
Tom Ogilvy



wrote in message
ps.com...
Here is what I was looking for

Suppose Cell B1= 2 , then I wanted to cells C1 and D1 to be blank and
locked ( Locked in the sence, no one should be able to write in those
cell. They should be as good as merged cell where I can't even blink my
cursor)

So depending on the values of the cell in column B, I wanted to make
the equivalenmt cells in that row as dead locked.
Suppose B1 = 3 then C1 D1 and E1 should be dead locked.
Suppose B2 = 4 then C2, D2, E2 and F2 should be dead locked.

Dead locked means no one should be able to write in nor format those
cell.