pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Hi can any help with a formula to highlight text within a range that is equal to todays date

eg Range H4:N9

todays date is 28th Jan so the value in cell N8 (28) is auto highlighted in red

M
TWTFSS
1234567
891011121314
15161718192021
22232425262728
293031



<colgroup><col style="mso-width-source:userset;mso-width-alt:1097; width:23pt" span="7" width="30"> </colgroup><tbody>
</tbody>
any help would be apprieciated

regards

pwill
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,007
Office Version
  1. 365
Platform
  1. Windows
You could CF with this formula
=H4=DAY(TODAY())
 

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Thankyou Fluff, that worked great.

much appriciated

regards,

pwill
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,007
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Pwill:

I have helped you with other things.
Here is a little script I wrote which will put a calendar on your sheet and highlight todays date.

Just thought you may want to try it,
I store it in my Personal Folder so it's always available no matter what workbook I have open.

Code:
Sub My_Calendar()
'Version 1 9/23/15
ans = InputBox("What Range Should First Cell Be ?" & vbNewLine & "If left empty will use active cell", , "E7")
If ans = "" Then ans = ActiveCell.Address
Range(ans).Resize(5, 7).Select
Selection.Name = "Month"
Range("Month").Font.Size = 16
Dim z As Integer
z = 0
Dim Del As Variant
Del = Array("Sun", "Mon", "Tue", "Wed", "Thur", "Fri", "Sat")
MN = DateSerial(Year(Date), Month(Date), 1)
    'MsgBox Month
    For Each c In Range("Month")
        
        'MsgBox MN
        'c.Value = (MN - Weekday(Cells(1, 1).Value - 1) + Count)
        c.Value = (MN - Weekday(MN - 1) + Count)
        
        
        Count = Count + 1
    Next
    With Range("Month")
       
        .Interior.ColorIndex = 5
        .Find(What:=Date).Select
        ActiveCell.Interior.ColorIndex = 4
        End With
z = -1
Range("Month").Offset(-1).Resize(1, 7).Select
    For Each c In Selection
    z = z + 1
    c.Value = Del(z)
    Next
Range("Month").Columns.AutoFit
Selection.Interior.ColorIndex = 6
Selection.Font.Size = 16
Selection.HorizontalAlignment = xlCenter
Range("Month").NumberFormat = "m/d/yy"
Range("Month")(1).Select
End Sub
 

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Hi My Aswer Is This,

Thank you for that, I was just introducing my daughter to excel and she wanted to make a calender so your code will be a big help to us

I know its a bit late but I have been waiting all day for my daugher to finish her calender lol

You said you would help me with adding the 'For i =' into my macro to help me understand how it works and so I have put together the best I can to show you what I trying to do.

I add data to sheet1 - Tab name(Updates) and copy over the data to sheet2 - Tab name(Data)

On Sheet1 I have headers A1:G1 as A B C D E F G H and I add rows of data from A2:G
On Sheet2 I have Dates that run from A2:A with a header in A1, and a count that runs from D2:D
E2:L on sheet2 has data copied from Sheet1 with headers E1:K1 as A B C D E F G and H1 Used
AI2:AQ2 on sheet2 are linked to Columns A2: D2: E2: F2: G2: H2: I2: J2: K2 so I can auto fill down the data from the columns A2:K

The data copied from Sheet1 is added to the last row E:K on Sheet2 and then I autofill colums AI2:AQ2 down the number of rows added from Sheet1.
So if I copied A2:G16 15 rows would be added to columns E:K from the last row.

I will add examples after this post.
In my example, Columns E2:K12 Sheet2 already has data, so if I copy the rows from Sheet1, the data will run E2:K26.

I am trying to add code to the macro that copies the data from Sheet1 to include autofill Columns AI2:AQ2 down the number of rows from Sheet1 A2:K with data.
 

pwill

Active Member
Joined
Nov 22, 2015
Messages
406

ADVERTISEMENT

eg

Sheet2 with the data already

Date/YearABCDEFGusedDate/YearABCDEFG
01/01/1811234567110/01/18101234567
02/01/1821234567111/01/18110000000
03/01/18312345671
04/01/18412345671
05/01/18512345671
06/01/18612345671
07/01/18712345671
08/01/18812345671
09/01/18912345671
10/01/181012345671
11/01/18110000000
12/01/1812
13/01/1813
14/01/1814
15/01/1815
16/01/1816
17/01/1817
18/01/1818
19/01/1819
20/01/1820
21/01/1821
22/01/1822
23/01/1823
24/01/1824
25/01/1825

<tbody>
</tbody>


I will add more examples next
 
Last edited:

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Sheet1 with the data to be copied

ABCDEFG
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
0000000

<tbody>
</tbody>

Sheet2 with the data added from Sheet1 and Autofilled down

Date/YearABCDEFGusedDate/YearABCDEFG
01/01/1811234567110/01/18101234567
02/01/1821234567111/01/18111234567
03/01/1831234567112/01/18121234567
04/01/1841234567113/01/18131234567
05/01/1851234567114/01/18141234567
06/01/1861234567115/01/18151234567
07/01/1871234567116/01/18161234567
08/01/1881234567117/01/18171234567
09/01/1891234567118/01/18181234567
10/01/18101234567119/01/18191234567
11/01/181112345671
20/01/18201234567
12/01/181212345671
21/01/18211234567
13/01/181312345671
22/01/18221234567
14/01/181412345671
23/01/18231234567
15/01/181512345671
24/01/18241234567
16/01/181612345671
25/01/18250000000
17/01/181712345671
18/01/181812345671
19/01/181912345671
20/01/182012345671
21/01/182112345671
22/01/182212345671
23/01/182312345671
24/01/182412345671
25/01/18250000000

<tbody>
</tbody>

I will add the macro I am using next
 
Last edited:

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
This is the macro I am tring to add code to and I am trying to use 'For i = 2 to lRow Sheet1' as the number of rows to autofill on Sheet2

Code:
Sub Copy_Data()

Dim wsUpdates As Worksheet: Set wsUpdates = Sheet00
Dim wsData As Worksheet: Set wsData = Sheet01
Dim lRow As Long
Dim lRowK As Long
Dim lRowAS As Long
    
    Application.ScreenUpdating = False
    
    lRow = wsUpdates.Cells(Rows.Count, "A").End(xlUp).Row
    lRowK = wsData.Cells(Rows.Count, "K").End(xlUp).Row
    lRowAS = wsData.Cells(wsData.Rows.Count, "AS").End(xlUp).Row
    
wsUpdates.Activate
        Range("H2:H" & lRow).Value = 1
        
        Range("A" & lRow + 1).Value = 0
        Range("B" & lRow + 1).Value = 0
        Range("C" & lRow + 1).Value = 0
        Range("D" & lRow + 1).Value = 0
        Range("E" & lRow + 1).Value = 0
        Range("F" & lRow + 1).Value = 0
        Range("G" & lRow + 1).Value = 0
        
        Range("A2:H" & lRow + 1).Copy
        
        Range("A1").Select
        
    wsData.Activate
        Range("E" & lRowK).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        
        'For i = 2 To lRow
        'Range("AI2:AQ2").AutoFill Destination:=Range("AI2:AQ" + i)
        'Next
        
        wsUpdates.Range("A2:H" & lRow + 1).ClearContents
        
        Application.ScreenUpdating = True
        
End Sub

The part in green is what obviously where I am trying to add the 'For i ='

Any help would be apprieciated

regards

pwill
 

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
I should have said

Sheet1 with the data to be copied before the zero's are added with the macro

ABCDEFG
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567

<tbody>
</tbody>
 

Forum statistics

Threads
1,148,293
Messages
5,745,912
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top