Indenting cells with VBA where value in another cell matches criteria

**barefaced66** · 01-16-2015, 12:19 PM

Hi

Firstly, I would just like to confirm that I have searched until my eyes bled and haven't been able to find what I need to resolve my problem!

Secondly, what I am trying to achieve is to have the text in a cell indent automatically if the value in another cell meets a specific criterion. I have attached the relevant worksheet from a wider workbook (nothing in the other worksheets relates to this particular workbook).

plan.xlsm

The objective is that if a project manager selects L3 as a value in column B, the text in column E will indent by 1 (signifying that the entry is a sub-task or milestone of the L2 milestones which the entry should sit under). I found some VBA which I thought would do the trick and I have left that in the workbook and also posted it here (apologies because I have lost track of where it originated in the first place) but I can't get it working.

Please Login or Register  to view this content.

I would prefer not to rely on users creating their plans with relevant milestone levels and then pressing a button to run a macro to indent the relevant cells but, if the consensus is that it is the best way to go, I am happy to follow the crowd. Grateful for any help.

Thanks
Nicki

**bart hoekstra** · 01-16-2015, 12:40 PM

I don't quite understand the problem!
Col B has a DDbox attached. I can't see any numeric values under it.
OK
If you enter 10 in column B : Do you want to indent row 16 by 1 indent?

**barefaced66** · 01-17-2015, 10:36 AM

Originally Posted by bart hoekstra

I don't quite understand the problem!
Col B has a DDbox attached. I can't see any numeric values under it.
OK
If you enter 10 in column B : Do you want to indent row 16 by 1 indent?

Bart - my bad. There is a hidden helper column C which contains a formula so that if the user selects L2 in Column B, the helper cell value is 0 and if they select L3, the helper cell value is 1 - then I want to reference the value in the helper cell via VBA to indent the entry in column E - does that make more sense?

N

**bart hoekstra** · 01-18-2015, 10:42 AM

Try this
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("D:D"), Range(Cells(Target.Row, 1), "$D" & Target.Row)) Then
If Range("$D" & Target.Row).Value > 0 Then Range("$e" & Target.Row).IndentLevel = Range("$D" & Target.Row).Value
End If
End Sub

**bart hoekstra** · 01-18-2015, 10:43 AM

Try this
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("D:D"), Range(Cells(Target.Row, 1), "$D" & Target.Row)) Then
If Range("$D" & Target.Row).Value > 0 Then Range("$e" & Target.Row).IndentLevel = Range("$D" & Target.Row).Value
End If
End Sub

**bart hoekstra** · 01-18-2015, 10:47 AM

Hi here's the modified sheet:

**barefaced66** · 01-18-2015, 02:40 PM

Originally Posted by bart hoekstra

Hi here's the modified sheet:

That looks perfect, thank you
N

**barefaced66** · 01-18-2015, 02:40 PM

Originally Posted by bart hoekstra

Hi here's the modified sheet:

That looks perfect, thank you
N

**barefaced66** · 01-19-2015, 05:04 AM

Ok, not quite solved yet. How do I amend the VBA so that if a user selects L3 in Col B (and so the contents of the cell in Col E are indented) and then changes the entry in Col B back to L2, the contents of the cell in Col E revert back to being 0 indented?

Thanks
N

**bart hoekstra** · 01-19-2015, 09:14 AM

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("D:D"), Range(Cells(Target.Row, 1), "$D" & Target.Row)) Then
Range("$e" & Target.Row).IndentLevel = Range("$D" & Target.Row).Value
Else
If Range("$e" & Target.Row).IndentLevel > 0 Then Range("$e" & Target.Row).InsertIndent -1
End If
End Sub

**barefaced66** · 01-19-2015, 10:46 AM

Bart - you are a star, thank you!

Indenting cells with VBA where value in another cell matches criteria

LinkBack

Thread Tools

Rate This Thread

Display

Indenting cells with VBA where value in another cell matches criteria

Re: Indenting cells with VBA where value in another cell matches criteria

Re: Indenting cells with VBA where value in another cell matches criteria

Re: Indenting cells with VBA where value in another cell matches criteria

Re: Indenting cells with VBA where value in another cell matches criteria

Re: Indenting cells with VBA where value in another cell matches criteria

Re: Indenting cells with VBA where value in another cell matches criteria

Re: Indenting cells with VBA where value in another cell matches criteria

Re: Indenting cells with VBA where value in another cell matches criteria

Re: Indenting cells with VBA where value in another cell matches criteria

Re: Indenting cells with VBA where value in another cell matches criteria

Thread Information

Users Browsing this Thread

Similar Threads

Tabbing/Indenting within a cell

[SOLVED] Populating cells with data that matches certain criteria

Sum cells in 1 col if criteria in another col matches row below

If values in 2 cells matches criteria, add text to a third cell

Indenting cells in Excel

Bookmarks

Bookmarks

Posting Permissions