VBA: Indenting cells in one column based off data in another column.

[mrkevelev]

So I have a list of values in a column with named "Rank". This has values of integers going from 1 to 10. In a different column called "Name" I want to indent the data in each cell according to the value in column "Rank." So if Rank = 1, no indent is needed. If Rank = 2, one indent (5 spaces) is needed. If rank = 3, 10 spaces are needed, if 4, then 15 spaces, etc. So I want this to work based off the column title, and not a specific location in the worksheet, as the columns may not be in the same location every time. Sometimes I need to adjust the rank as well, and it would be nice if the indents followed along (I know how to do this "live update" with conditional formatting, maybe incorporate this somehow?).

Example:

Rank Name
1 Bob
2 Tim
2 Tom
2 Steve
3 Mike
2 Dave
3 Josh
1 Joe
1 Dan
2 John
3 Jim
4 Rick

 

[mrkevelev]

Update: The example didn't save the indents, but for each number increased, add 5 spaces to the indent. (Starting with a 1 as being 0 spaces).