Indent in relation to numeric cell value

All,

Attached is a sample spreadsheet created in Excel 2010.

Column A is an excerpt from a large workbook I am working on. My goal is to have the data in Column A indent based on its numerical value.

I was eperimenting and created an IF arguement in Column B; which somewhat accomplishes this. My concen is that if you hid column A, the formula in column B could be altered by accident.

I've tried to create a conditional formatting for this; to no avail. Any help would be appreciated!

Is there some reason you don't just want to highlight col B and use the alignment button in the Home tab?

Sambo kid,

I don't want to indent it manually. Besides there are times when the numeric values are as high as 14. My goal would be to have the condition result in - cell value =0; indent 0; cell value =1; indent 1 etc.

Ok, just wanted to check that. Sometimes the most obvious answer is overlooked.
So just to be a little more clear, 0 isn't indented, 1 is bumped in one space, 2 - 2 spaces, 3 - 3 spaces etc? Is that correct?

Yes sir, that is correct

That'll help whoever is able to solve this one. If you don't want to use a formula (because my guess would have been something along the lines of where you went or a substitute formula), I tried a find and replace but haven't gotten that to work yet.

ARGK,

Thank you for that! It seems to work flawlessly. One benifit is this code will work on non-.xslm files. There would not be any need to enable macros.

Attached is a macro code one other guy in my office created me, in tandem with this posting.

Function SetIndent(r As Range, ByVal Level As Long) As Variant
    ' shg 2008
    
    ' Sets the indent level of r from 0 to 15 and returns the indent level
    
    Dim cell    As Range

    SetIndent = IIf(Level < 0, "Min is 0!", IIf(Level > 15, "Max is 15!", Level))
    If Level < 0 Then Level = 0 Else If Level > 15 Then Level = 15
    
    For Each cell In r
        With cell
            If Level - .IndentLevel Then .InsertIndent Level - .IndentLevel
        End With
    Next cell
End Function

why not just use in col b
=REPT(" ",A2)&A2
unlock all cells
then hide col a
then lock cells and hide formulas in column b only
then protect sheet

martindwilson,

That seems to work very well. Depending on what you are doing you could modify the protection of the worksheet to select, modify cells when protected. Thanks.

you just unprotect when you need,there are lots of options in protect must admit i havent tried them all but you only have to lock column b