Tags: apply, based, cell, chart, conditional, excel, formatting, indentation, microsoft, msdn, software, text
Conditional formatting - based on indentation
On Microsoft » Microsoft Excel
9,428 words with 6 Comments; publish: Wed, 04 Jun 2008 18:10:00 GMT; (30662.50, « »)
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?
http://excel.itags.org/q_microsoft-excel_81006.html
All Comments
Leave a comment...
- 6 Comments
- 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
<benny.watt.excel.itags.org.gmail.com> wrote in message
news:7ea2f3fb-12d4-43eb-aa1f-8cf59273c42a.excel.itags.org.e10g2000prf.googlegroups.com...
> 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?
#1; Wed, 04 Jun 2008 18:11:00 GMT
- 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
>
> <benny.watt.excel.itags.org.gmail.com> wrote in message
> news:7ea2f3fb-12d4-43eb-aa1f-8cf59273c42a.excel.itags.org.e10g2000prf.googlegroups.com...
> > 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?
>
>
#2; Wed, 04 Jun 2008 18:12:00 GMT
- 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.
#3; Wed, 04 Jun 2008 18:13:00 GMT
- 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
>> <benny.watt.excel.itags.org.gmail.com> 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?
#4; Wed, 04 Jun 2008 18:14:00 GMT
- On Mar 10, 5:11 pm, "H=E9ctor Miguel" <NOhemiordiS....excel.itags.org.PLShotmail.com>
wrote:
> check for the <range>.IndentLevel (from 0 to 15)
> hth,
> hector.
>
works.. !
#5; Wed, 04 Jun 2008 18:15:00 GMT
- 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" <OssieMac.excel.itags.org.discussions.microsoft.com> wrote in message
news:42363CAA-BBF6-48F1-9747-0808C1E8C6B8.excel.itags.org.microsoft.com...
> 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
>>
>> <benny.watt.excel.itags.org.gmail.com> wrote in message
>> news:7ea2f3fb-12d4-43eb-aa1f-8cf59273c42a.excel.itags.org.e10g2000prf.googlegroups.com...
>> > 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?
>>
#6; Wed, 04 Jun 2008 18:16:00 GMT