Indentation

[CB]

How can I indent a cell if another cell contains certain Data.

Example I want to
Indent C1 once if Cell D1 = Detroit
Indent C1 Twice if Cell D1 = Toronto

The sheet may have 400 row.

Any thoughts?

 

[Shane Devenshire]

Hi,

You will need to use code. Here is some sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("D1400"))
If Not isect Is Nothing Then
'Your code here
End If
End Sub

Record the indent command and then place it into "Your code here

1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code above.

 

[Shane Devenshire]

Hi,

I only had a few minutes earlier because I was on lunch, but here is your
code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("D1100"))
If Not isect Is Nothing Then
If Target = "Detroit" Then ActiveCell.Offset(0, -1).InsertIndent 1
If Target = "Toronto" Then ActiveCell.Offset(0, -1).InsertIndent 2
End If
End Sub

 

[Jarek Kujawa]

without going to VBA
maybe sth like this would prove helpful:

=IF(D1="Detroit",REPT(CHAR(32),5)&B1,IF(D1="Toronto",REPT(CHAR(32),10)&B1,))

presume your data re city in D1 is in B1=252
then if D1 is Detroit this formula inserts 5 spaces before 252
if D1 is Toronto this formula inserts 10 spaces before 252

result of this formula will be text only so if you need to make some
calculations add 0 to them or multpiply them by 1 like here

=IF(D1="Detroit",REPT(CHAR(32),5)&B1,IF(D1="Toronto",REPT(CHAR(32),10)&B1,))*1

pls click YES if this was helpful