Format cell with loop

[Hyakkivn]

Hi everyone, please help me point out the error on this code please:

Sub format()
dim nd as range, cell as range, ws as worksheet
set ws = Activesheet
set nd = ws.range("B10:E30") ' B10:E10 to B30:E30 are merge cell
For Each cell in nd
IF cell.Value = ws.Range("L22").Value or cell.value = ws.Range("L23").value Then 'there are more value than this...
cell.Offset(0, 2).NumberFormat = "General"
ELSE
cell.Offset(0, 2).NumberFormat = "#,##0.00"
End If
Next
End Sub

When I called the sub above with a Private sub worksheet change at range B10:B30, the code just did the part after ELSE that format all cell at F column to #,##0.00, not just the cell I input value; and the first part before ELSE was not working

 

[Alex Blakenburg]

Which actual cells are merged ?
eg B10:E10, B11:E11 etc ?

 

[mohadin]

Try

Sub format()
    Dim nd As Range, cell As Range, ws As Worksheet
    Set ws = ActiveSheet
    Set nd = ws.Range("B10:B30")    ' B10:E10 to B30:E30 are merge cell
    For Each cell In nd
    cell.Select
        If cell.Value = ws.Range("L22").Value Or cell.Value = ws.Range("L23").Value Then    'there are more value than this...
            cell.Offset(0, 2).NumberFormat = "General"
        Else
            cell.Offset(0, 2).NumberFormat = "#,##0.00"
        End If
    Next
End Sub

 

[Alex Blakenburg]

I think you have a couple of typos.
I am guessing you mean B10:E10 (not row 30) and set nd = ws.Range("B11:E30")

 

[bebo021999]

Hi everyone, please help me point out the error on this code please:

When I called the sub above with a Private sub worksheet change at range B10:B30, the code just did the part after ELSE that format all cell at F column to #,##0.00, not just the cell I input value; and the first part before ELSE was not working

B10:E10 are merged, that mean only B10 get value that displays on merged cell.
Thus, B10 get change, then B10.offset(0,2) (= D10) get effect, not column F.
Change to offset(0,4) to reach column F.

 

[Hyakkivn]

Which actual cells are merged ?
eg B10:E10, B11:E11 etc ?

B10 to E10 are merged, same with B11 to E11... B30 to E30.

 

[Hyakkivn]

Try

Sub format()
    Dim nd As Range, cell As Range, ws As Worksheet
    Set ws = ActiveSheet
    Set nd = ws.Range("B10:B30")    ' B10:E10 to B30:E30 are merge cell
    For Each cell In nd
    cell.Select
        If cell.Value = ws.Range("L22").Value Or cell.Value = ws.Range("L23").Value Then    'there are more value than this...
            cell.Offset(0, 2).NumberFormat = "General"
        Else
            cell.Offset(0, 2).NumberFormat = "#,##0.00"
        End If
    Next
End Sub

It still formats from G10 to G30 (yes, from G10 then G11...G30) with #,##0.00 format.
I want that when I put value on B10 for example, then bases on value of B10, the cell G10 will be formatted to general or number with decima.

 

[mohadin]

Any way for column G
Try

Sub format()
    Dim nd As Range, cell As Range, WS As Worksheet
    Set WS = ActiveSheet
    Set nd = WS.Range("B10:B30")    ' B10:E10 to B30:E30 are merge cell
    For Each cell In nd
        cell.Select
        If cell.Value = WS.Range("L22").Value Or cell.Value = WS.Range("L23").Value Then    'there are more value than this...
           Cells(cell.Row, 7).NumberFormat = "General"
        Else
            Cells(cell.Row, 7).NumberFormat = "#,##0.00"
        End If
    Next
End Sub

 

[Hyakkivn]

Any way for column G
Try

Sub format()
    Dim nd As Range, cell As Range, WS As Worksheet
    Set WS = ActiveSheet
    Set nd = WS.Range("B10:B30")    ' B10:E10 to B30:E30 are merge cell
    For Each cell In nd
        cell.Select
        If cell.Value = WS.Range("L22").Value Or cell.Value = WS.Range("L23").Value Then    'there are more value than this...
           Cells(cell.Row, 7).NumberFormat = "General"
        Else
            Cells(cell.Row, 7).NumberFormat = "#,##0.00"
        End If
    Next
End Sub

It finally works. Thank you so much !!!

 

[mohadin]

You are very welcome
And thank you for the feedback
Be happy and safe