Merge consecutive cells using conditional formatting.

[bhavik123]

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.

 

[Tom Ogilvy]

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