Conditional formatting - based on indentation
 

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?

Conditional formatting - based on indentation
 

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



--


Gary


wrote in message
...
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?

Conditional formatting - based on indentation
 

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.


--
Regards,

OssieMac


"Gary Keramidas" wrote:

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



--


Gary


wrote in message
...
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?

Conditional formatting - based on indentation
 

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.

Conditional formatting - based on indentation
 

hi, guys !

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

hth,
hector.

OssieMac wrote in message ...
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.

Gary Keramidas wrote:
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

--
Gary

wrote in message ...
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?

Conditional formatting - based on indentation
 

On Mar 10, 5:11 pm, "Héctor Miguel"
wrote:

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


works.. !

Conditional formatting - based on indentation
 

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

--


Gary


"OssieMac" wrote in message
...
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.


--
Regards,

OssieMac


"Gary Keramidas" wrote:

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



--


Gary


wrote in message
...
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?