Conditionally Indent cell

[mr_hiboy]

Hi,


I have a column of data B14:B10000 (in fact this is a merged column with column C, i.e. B14:C14 merge to hold the info, as I need to cells above).


The cells will either contain text or a date, if it's text I'd like to indent by 1, if it is a date indent by 3. It's a product/sub product list.


I'm assuming I'll need to VB for this?


Thanks in advance

 

[DaveTurton]

[pre]

Sub indent()
Dim i As Integer

For i = 14 To 1000
If IsDate(Cells(i, 2).Value) Then
Cells(i, 2).IndentLevel = 3
Else
Cells(i, 2).IndentLevel = 1
End If
Next i
End Sub

[/pre]

 

[mr_hiboy]

Thanks for the quick response Dave.


How do i get it do the indent on exist of the cell?

 

[DaveTurton]

do you mean automatically apply the indent when data is entered rather than looping?

 

[mr_hiboy]

Yes. I meant to say exit cell, not exist!

 

[DaveTurton]

but the below in the sheet code:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row >= 14 And Target.Row <= 1000 And Target.Column = 2 Then

If IsDate(Target.Value) Then
Target.IndentLevel = 3
Else
Target.IndentLevel = 1
End If

End If

End Sub

[/pre]

 

[mr_hiboy]

Works a treat!


Cheers Dave, appreciated


(not sure how to close a post)

 

[mr_hiboy]

Hi Dave (or anyone),


Just noticed when I delete the content of a cell i get an error


Runtime error 1004

Unable to set the IndentLevel property of the Range class


Assume it just needs a clause to state if it's blank do nothing (or leave at indent 1)? My VBA not quite there!


Cheers

 

[SirJB7]

Hi, mr_hiboy!

Checked the last code from DaveTurton and works fine.

https://dl.dropboxusercontent.com/u/60558749/Conditionally%20Indent%20cell%20-%20Sparkline-1%20%28for%20mr_hiboy%20at%20chandoo.org%29.xlsm

Regards!