[VBA]how to update date in selected cells

[jovie]

Hi I am very new of VBA, I would like to write a programme that when I click the button I set, it will update the date in selected cells instead of all the cells in the range I choose.

 

[Joe4]

Update the date to what? The current date?

 

[jovie]

Yup, I would like to update the date to current date, I only can update certain range like from A1 to A100, however during this range I have some cells that I dun want that update.

 

[Joe4]

So what is the logic for which cells should be updated and which cells shouldn't?

 

[jovie]

So what is the logic for which cells should be updated and which cells shouldn't?

Er..... base one Color, I would like to update the date only on those not fill with color

 

[jovie]

*base on

 

[alansidman]

maybe this:

Option Explicit


Sub fillNoColor()
    Dim c As Range
    Dim rng As Range
    Set rng = Range("A1:A19") 'change range as required
    
    Application.ScreenUpdating = False
    For Each c In rng
    If c.Interior.ColorIndex <> 6 Then 'change colorindex as required. This is yellow
    c.Value = Date
    End If
    Next c
    Application.ScreenUpdating = True
    
    
End Sub

 

[jovie]

maybe this:

Option Explicit


Sub fillNoColor()
    Dim c As Range
    Dim rng As Range
    Set rng = Range("A1:A19") 'change range as required
    
    Application.ScreenUpdating = False
    For Each c In rng
    If c.Interior.ColorIndex <> 6 Then 'change colorindex as required. This is yellow
    c.Value = Date
    End If
    Next c
    Application.ScreenUpdating = True
    
    
End Sub

I think this will be very useful, but I am not with my laptop now, so I can't try the code now. However I wonder to know that is there have anyway to update the date in one row that I write sth today, and next time when I write sth in next row it will update the date of that row for the current row

 

[Joe4]

OK. Try this. If you select the range of cells you want to apply it to and run this, it will put the date in any cell that does not have any color fill (regardless of what color fill you may be using):

Sub MyDateStamp()

    Dim cell As Range
    
    Application.ScreenUpdating = False
    For Each cell In Selection
        If cell.Interior.Pattern = xlNone Then
            cell = Date
        End If
    Next cell
    Application.ScreenUpdating = True
            
End Sub

So it will work on any selected range and any color fill without having to adjust anything in the code.

 

[jovie]

OK. Try this. If you select the range of cells you want to apply it to and run this, it will put the date in any cell that does not have any color fill (regardless of what color fill you may be using):

Sub MyDateStamp()

    Dim cell As Range
    
    Application.ScreenUpdating = False
    For Each cell In Selection
        If cell.Interior.Pattern = xlNone Then
            cell = Date
        End If
    Next cell
    Application.ScreenUpdating = True
            
End Sub

So it will work on any selected range and any color fill without having to adjust anything in the code.

Best regard, Really, thanks