Perfect, you never cease to amaze me....
OK I'm back again, sorry.
My workbook is getting full and the MsgBox is also getting full, at the moment I have found that if I hide some columns, the MsgBox it only shows information for un-hidden columns. Is there a way to have a static sized form (possibly a UserForm) with a scroll bar to allow for users to see all the relavent information?
royUK, thanks for this but it just has the active cell as yellow?
I conditional formatted some cells to green if number 2 was entered. I then activated your code above, it didn't work on ther formatted cells, the color didn't change from the conditional format?
Thanks royUK. My apologies, I have created a new thread on my subject.
Thanks for the response alansidman, however, the cells are different color formats dependant on text/value. Your solution would just turns them all back to light blue.
I think the logic would be - on cell activation to record/copy current format and hold it in memory, change format to green, on exit, paste format from memory. Not sure how to do this in VBA?
I have several workbooks I use for recording minutes of meetings. Each workbook sheet has specific conditional formatting to color cells based on text or value. During the meetings I would like the active cell to be bold enough so the viewers can focus on the actions. I am looking for a VBA code to highlight the active cell and when I move to another cell, the previus fomatting remains. An example would be, Cell A1 is formatted light blue, when I select A1 the cell turns green, when I select another cell, A1 returns to light blue. Hope that makes sense.
Thanks in advance
royUK, I did a search for VBA code to highlight just the active cell and came across this thread. Is there a simplified code just for the active cell, or should I submit as a seperate thread?
Dandelor, this is interesting about having a checkbox in a my custom ribbon, can you tell me how to add a checkbox to the ribbon?
This works excellent. You are a genius. Thanks so much for this, you just made my worksheet amazing.
Sorry, forgot to upload the revised worksheet
I was looking to develop this worksheet further. Is is possible that if you have multiple entries in the calendar section to either select the cell in column D or hover over it (prefered) to bring up a form showing a list of all the calendar entries with dates for that particular row?
Thanks again in advance
Mumps, this works exactly as I need it to. Thank you very much...
I have a spreadsheet which I'm trying to develop for a timeline for my projects. In the example attached columns A-C will be the static data, then Columns F onwards will be dated in the header row.
The data will be entered in the relevent row under the specific date, as per the examples. Each row may have many entries.
What I'm looking for is to have in column D is the date of the last entry in the row followed by a colon folllowed by the row cell data. Is this possible with a formula or would it be with VBA code?
Thank you in advance
I also tried:
It showed me the address but the hyperlink still didn't work.
Is there a VBA code where when I enter a document reference on column 'O' it creates the hyperlink to the corresponding document referenced in the other worksheet?
Hi, I'm looking to use the HYPERLINK and VLOOKUP Functions to hyperlink to a cell on another worksheet. I have a worksheet (DATA) with document numbers in column 'O', and a worksheet (DRN) with a list of document numbers in column 'B'. I want to hyperlink the document number in column 'O' of the DATA worksheet and it take me to the document number in the 'DRN' worksheet. I typed in the formula
but it just says 'cannot find file'. Can you help me to get this to work please?
I'm pretty new to this but I had a similar issue to you previously and managed to work out with the help of the forum a solution that might work for you.
Set the criteria
In my example we look for the words 'Electrical' in column E, 'DUE' in column AA, but Column AB doesn't have 'SENT' inserted. This means we need to send an email.Code
- Sub emailTask()
- Dim dateRow As Integer
- With Sheets("WPP Register")
- dateRow = .Range("AA4").End(xlDown).Row
- For i = 4 To dateRow
- If .Range("E" & i).Value = "Electrical" And .Range("AA" & i).Value = "DUE" And Not .Range("AB" & i) = "SENT" Then
- 'When the criteria is met it will run the code below
- Call emailMe(i)
- End If
- Next i
- End With
- End Sub
Then we need to set up the email, subject, body and recipients.Code
- Sub emailMe(i)
- Dim OutApp As Object
- Dim OutMail As Object
- On Error GoTo errorKey
- Set OutApp = CreateObject("Outlook.Application")
- Set OutMail = OutApp.CreateItem(0)
- On Error Resume Next
- With OutMail
- 'Take the details of the cell as the subject
- .Subject = Sheets("WPP Register").Range("AA1") & ""
- 'Creates the body of the email based on cell values
- .Body = Sheets("WPP Register").Range("E" & i) & " - " & Sheets("WPP Register").Range("F" & i) & " - " & Sheets("WPP Register").Range("AC1") & ""
- 'Insert the recipients to be emailed
- .Recipients.Add ("[email protected]")
- 'Add additional email addressess to suit
- '.Recipients.Add ("[email protected]")
- '.Recipients.Add ("[email protected]")
- .Send 'direct send, if you want manual send, delete this row
- End With
- 'Inserts the word 'SENT' into the worksheet so it doesn't send more emails on that issue
- Sheets("WPP Register").Range("AB" & i) = "SENT"
- Set OutMail = Nothing
- Set OutApp = Nothing
- Exit Sub
- MsgBox Err.Description
- Resume ContinueIt
- End Sub
Hope this helps
I've moved on to a new project and need a little help. I have a schedule of dates to track document submissions and at present I have to go through it line by line to see what is next due, importantly what is due from me. I would like to see the table represented on a timeline to make it easier for me to report upstream.
The attachment is a snapshot of how the data is currently maintained.
Any guidance or examples of how to get this into a timeline would be gratefully received.
Re: Lost Time Calculation
Thank you Carim, you're a genius.
I'm strugling with my workbook where I need to calculate how much time is lossed on my project.
My workbook has a 'planned start' time a 'planned finish' time, an 'actual start' time and an 'actual finish' time, all these four times are automatically extracted from a separate report submitted every morning after the shift.
My problem is these shifts run overnight i.e. 23:55 - 05:00, which is where I encounter the problem. I know it would be easy if the cells were date/time formatted, but the reports come from many departments and it would take ages to re-format all the other workbooks. They simply put in the times in the cells which I need to extract.
In addition to this, I need to calculate the time lost between the planned and actual times. I have attached a workbook with an example of what I'm faced with, the first 3 rows have been manually inserted. What I need is the highlighted cells to contain a formula to make the calculations work?
I hope someone can help as this would save me hours of manually working out the total hours and the amount we are losing?
Re: Formula showing in cell
Thank you very much for your help, this works like a charm