Auto color fill unpopulated cells in a table and add time beside entries

[smsmworld]

hello
i have a table that consists of 21 column from A to U and 31 rows. Column headers are named per day from B1 (Day 1) to U1 (Day 20) and these columns will be filled on daily basis over 30 entries.
my question is how can i make Excel auto color fill empty cells on any given column conditioned that it doesn't do that unless we start populating a cell in the next column?
so for example, someone will populate 21 cells out the 30 in column A. what i want is that for the remaining 9 cells to be filled with Red for example only when we start adding a data entry in a cell under Column B. and the same will happen to B when we move to C and so on.
Also, is there a way to make Excel add a timestamp of the entry beside the data whenever we add data to any given cell?
Thanks!

 

[MartyS]

A. what i want is that for the remaining 9 cells to be filled with Red for example only when we start adding a data entry in a cell under Column B. and the same will happen to B when we move to C and so on.

Could you clarify:

Do you mean

(A) The remaining 9 cells to be filled with Red only when data is added to ANY cell in column B

or


(B) The remaining 9 cells to be filled with Red only when data is added to column B in the same ROW as the missing data

Also, is there a way to make Excel add a timestamp of the entry beside the data whenever we add data to any given cell?
Thanks!

What do you mean by "beside the data"?

It is possible to have the Excel worksheet add a timestamp when data is entered into a cell.

The questions are:

(i) where specifically do you want that?


If you want it "beside" - as in "in a new column, your entire sheet structure will change"

If you want it "beside" - as in "in the same cell", the cell contents will read as whatever you entered plus the timestamp information.
NB: If this is numerical information, it will no longer be numerical information and you won't be able to perform calculations on it

An alternative might, then, be to add in a COMMENT to each cell when data is entered.


(ii) what format do you want the timestamp in (HH:MM... HH:MM:SS... DD/MM/YY HH:MM... and so on...)?

 

[MartyS]

If you want this:

Then do this!

i.e.

Select the range B2:U31 making sure that B2 is the start of the range.

(i.e. the top left cell will be white, whilst all the rest in the range will be blue)


Go to Conditional Formatting.

Click NEW RULE.

Choose from the list "Use a formula to determine which cells to format"

In the Format Values where this formula is true section, enter the formula =AND(LEN(TRIM(B2))=0,LEN(TRIM(C2))>0)

Choose the colour by clicking on the Format button..

Then click on OK when done!

 

[smsmworld]

Thank you all, really appreciate your help! i was able to work it out based on your replies and doing some digging on search.

 

[MartyS]

Also, is there a way to make Excel add a timestamp of the entry beside the data whenever we add data to any given cell?
Thanks!

In the VBA editor, go to the WORKSHEET that you are using (i.e. if your table that goes up to column U and down to row 31 is in SHEET1, then the following code needs to be entered into SHEET1 and NOT a Module)

Before adding this code, do check there isn't already any Worksheet_Change code. (Best to copy your current spreadsheet to a new spreadsheet, and test on the new one).

Note.. if, in the future, the number of rows changes from 31.. or the maximum column letter changes from "U", you can easily change this in the first four lines of code (CONST MaxCol and CONST MaxRow)

Private Sub Worksheet_Change(ByVal Target As Range)


'   Appends DATE to whatever is entered into a cell in the range defined by the first 4 constants below
'
'   N.B. Important: The date is in the format of "[DD-MM-YY HH:SS]" - these SQUARE BRACES are CRUCIAL
'   i.e. do NOT remove them if you reformat the date because they are used in evaluating what to do.
'   Also, do NOT enter text containing square braces into the spreadsheet
'   e.g. "[hello]"
'   if you want the date to be appended.


    Const MinCol = "B"
    Const MinRow = 2
    
    Const MaxCol = "U"
    Const MaxRow = 31


    
    Dim nMaxCol As Integer, nMinCol As Integer, nSelCol As Integer, nSelRow As Integer


    Dim Cnt As Integer
    Dim Cntr As Integer
    Dim CelArray() As String


    Dim Cel As Range
    
    Dim RangeArrayCheck


    'Get the number of cells selected
    Cnt = Target.Cells.Count


    'For every cell in the range of changed cells
    For Each Cel In Target.Cells
    
        'Get the address of each cell in the range
        If Len(Trim(Cel.Value)) > 0 Then
            
            With Cel
            
                'Get the variables that define max range and current range
                nMaxCol = ActiveSheet.Range(MaxCol & MaxRow).Column
                nMinCol = ActiveSheet.Range(MinCol & MaxRow).Column
                nSelCol = ActiveSheet.Range(Cel.Address).Column
                nSelRow = ActiveSheet.Range(Cel.Address).Row
                
                'Only record the cell to change if it is INSIDE the region to "append date" to
                If (nSelCol <= nMaxCol) And (nSelRow <= MaxRow) And (nSelCol >= nMinCol) And (nSelRow >= MinRow) Then
                    ReDim Preserve CelArray(1 To Cnt)
                    Cntr = Cntr + 1
                    CelArray(Cntr) = Cel.Address
                End If
            
            End With
            
        End If
    
    Next Cel
    
    Cnt = Cntr
    
    'Go through the array
    For Cntr = 1 To Cnt
        
        If (InStr(1, ActiveSheet.Range(CelArray(Cntr)).Value, "[") <> 0) And (InStr(1, ActiveSheet.Range(CelArray(Cntr)).Value, "]") <> 0) Then
            
            'Do nothing - date already in that cell (this is here because routine called a SECOND time - for the initial adding of the date!)
          Else
              
            'Add the date in DD-MM-YY HH:SS format, within square braces e.g. [dd-mm-yy hh:mm]
            ActiveSheet.Range(CelArray(Cntr)).Value = ActiveSheet.Range(CelArray(Cntr)).Value & " [" & Format(Now(), "dd-mm-yy HH:MM") & "]"
            
        End If
        
    Next Cntr
    
End Sub

----------------------------------------------------------------------------------------

Amendment to previous post about Conditional Formatting.

I made a slight mistake - you only need to highlight up to the penultimate (one from last) row, not the ultimate (last) row..

i.e. Only highlight up to column T31 before applying the conditional formatting. Column U doesn't need it (as nothing is entered into Column V that will impact highlighting for U)

Hope this helps.

Here's a sample spreadsheet that has both the code and the conditional formatting.

https://www.dropbox.com/s/mckimfra6tfsqxo/CondFormatandAppendDate.xlsm?dl=0