Conditional formatting - based on indentation

[benny.watt]

as per title..

I would like to apply conditional formatting based on the indentation
of text in a cell.

I have a chart of accounts in excel, with a parent-child hierarchy. I
want to format all top-level parents (i.e. no indentation) with bold
(or whatever formatting), then all second-level (i.e. one indent) with
italics.

Is this possible?

 

[Gary Keramidas]

here's an idea that may work.

i assumed the accounts were in column A and each child account had 3 spaces more
than the one above it.

Sub test()
Dim ws As Worksheet
Dim i As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
Select Case InStrRev(ws.Range("A" & i), " ")
Case 0
ws.Range("A" & i).Font.Bold = True
Case 3
ws.Range("A" & i).Font.ColorIndex = 5
ws.Range("A" & i).Font.Bold = False
Case 6
ws.Range("A" & i).Font.ColorIndex = 4
ws.Range("A" & i).Font.Bold = False
End Select
Next
End Sub

 

[OssieMac]

Hi Gary,

I have not tested it but I would be interested to know if your solution
really works with the Indent format. Excel does not appear to apply leading
spaces. It simply indents. This can be established using the LEN and LEFT
functions; neither of which indicate that there are any extra leading
characters.

 

[Héctor Miguel]

hi, benny !

I would like to apply conditional formatting based on the indentation of text in a cell.
I have a chart of accounts in excel, with a parent-child hierarchy.
I want to format all top-level parents (i.e. no indentation) with bold (or whatever formatting)
then all second-level (i.e. one indent) with italics.
Is this possible?

assuming "indentation" as simply characters at left of cells (i.e. spaces or any "pattern") -?-
try with format conditions, using your indentation "pattern" as the conditional-formulae

(tip):
if your pattern includes only spaces, use as first condition your last indentation level (and so on...)
and don't forget you have only 3 different format-conditions (plus "normal" format")
(unless you are using xl from office 12 with 64 levels for conditional formatting)

if any doubts (or further information)... would you please comment ?
regards,
hector.

 

[Héctor Miguel]

hi, guys !

check for the <range>.IndentLevel (from 0 to 15)

hth,
hector.

 

[benny.watt]

check for the <range>.IndentLevel (from 0 to 15)
hth,
hector.

works.. !

 

[Gary Keramidas]

no, i would only have worked if leading spaces were used. i missed he fact that
they used the format-indent function.
this would have been what i would have suggested if i would have read carefully.
sorrry

Option Explicit
Sub test()
Dim ws As Worksheet
Dim i As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
Select Case ws.Range("A" & i).IndentLevel

Case 0
ws.Range("A" & i).Font.Bold = True
Case 3
ws.Range("A" & i).Font.ColorIndex = 5
ws.Range("A" & i).Font.Bold = False
Case 6
ws.Range("A" & i).Font.ColorIndex = 4
ws.Range("A" & i).Font.Bold = False
End Select
Next
End Sub