How to edit the value of each cell of sheet in formula bar using macro


I am writing multiple rows to excel 2007 worksheet having different cell style for each column. Some of the cell showing a warning as top left corner of the cell with showing option in I want to select the option edit in the formula bar using a macro to format the cell value according to cell style/format.

Is there any way to loop through each cell showing such warning and selection edit in the formula bar. I don't want to ignore the warning instead I want to convert it into the format of cell style?

any suggestion??




Why don't you format the first complete row (Example row 2, after the header should be all data) and then copy the formatting down to all the other cells?  no need to iterate through each cell then.


Install the procedure given below in the code sheet of the worksheet on which you wish to control the number format. It will run every time you activate that sheet.

Private Sub Worksheet_Activate()
    Const SampleRow As Long = 2
    Dim Rng As Range
    Dim C As Long
    For C = 1 To UsedRange.Columns.Count
        Set Rng = Range(Cells(SampleRow + 1, C), _
                        Cells(Cells(Rows.Count, C).End(xlUp).Row, C))
        If Rng.Row > SampleRow Then
            Rng.NumberFormat = Cells(SampleRow, C).NumberFormat
        End If
    Next C
End Sub

Look for the constant "SampleRow" in the above code. If necessary change its value to the number of the row in which you have the correct format. The code will loop through all columns and apply the Numberformat found in the SampleRow to all cells in the column. It could be extended to set other formats. For example, replace the line setting the NumberFormat with the following 3 lines to copy all formats, including colors, fonts and borders.

           Cells(SampleRow, C).Copy
            Rng.PasteSpecial Paste:=xlPasteFormats
            Application.CutCopyMode = False

If your workbook only has one sheet (or if you prefer that kind of action) replace the first line - the declaration line - of the above procedure with

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

and place the procedure in the "ThisWorkbook" module. It will run when you save the workbook, meaning you will save the correct format.

You can force the code to run by switching sheets forth and back or, if attached to the Save event, by saving the workbook.

Despite of all the above I do agree with @queue that it is better to prevent wrong formatting than to repair it. Repair should only be necessary if you need to paste data and can't control the format of the data you paste. In all other cases I would prefer to search for a solution which ensures that cells are formatted corractly at the time of entering the data.


Answer the Question

You must create an account to use the forum. Create an Account or Login