Button Click Changing Specific Text in Specific Cells

[lockarde]

Evening all,

I'm working on a schedule for my boss, and I'd like to add a button that when clicked, changes the format of specific text in specific cells. This is for Time off, so say I have 3 days selected, and want to change "John" to have RED font, strikethrough the text, and increase font size to 18. This way, my boss can clearly see who has requested, and been given time off. I assumed "ActiveCell" would be the easiest way to go about this, but I'd need to select from a range of 5 employees, lets call them John, Sue, James, Trevor and Steve, and I'm having difficulty getting this right.

Any help is greatly appreciated!

 

[lockarde]

So I'm making a calendar for my boss, to show time off. What I'd really like, and what I'm trying to get to work is have check boxes to select employees, and then once an eployee is selected, choose a date that has that employee's name in it, and then click the button that changes that name's format to red, strikethrough, size 18. Font style isn't important, I just want to change the appearance. The example below is after "Neill" has been checked, and the "Time Off" button has been pushed. Each cell represents a day of the week.

[TABLE="class: grid, width: 950"]
<tbody>[TR]
[TD]Evan, Jaime, Neill



[/TD]
[TD]Evan, Jaime, Neill[/TD]
[TD]Evan, Jaime[/TD]
[TD]Evan, Todd, <strike>Neill</strike>
[/TD]
[TD]<strike>Neill</strike>, Todd
[/TD]
[TD]Jamie B, Todd[/TD]
[TD]**STORE**
**CLOSED**
*MERRY XMAS*[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

I have no ideal how to do this. Maybe someone else here at Mr. Excel will have an answer.

 

[lockarde]

No worries! I really appreciate you trying to help me out. I think you're on the right track though. I did some googling and found "Selection.Find" and I think that the solution may lie with that.. I'm fiddling, trial and error, with this:
(this is probably not correct/not how it interprets the CheckBox being checked)

If Todd_Checked = True Then
With Selection.Find
.Text = "Todd"
.Replacement.Text = "Todd Off"
.Size = 18
.Bold = True
.Strikethrough = True
.Color = vbRed
End With
End If

 

[My Aswer Is This]

I will keep monitoring this thread to see how it works out. I personally think putting several different fields (Employee Names) into one cell and then find that field can always be difficult. I would think it would be easier to add names to a cell (Date)

Now here is how I may do something:

I would create a range on my sheet which would be a Range of 31 cells
Then I would name that range "Calendar" and enter 1,2,3,4 etc. into each separate cell in the calendar range

Then for this example I put all the people who will be taking vacation on a particular day into the Range ("A1") and on down Column A as far as needed each cell having a different name

Then I would run this script. It will ask you for the date you would enter "23" for example and each person wanting vacation on that day would have their name entered into the proper cell in the calendar

And this is just a sample it could be modified to work with several different dates at once.

Sub Test()
Application.ScreenUpdating = False
Dim c As Range
Dim d As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim sss As String
Dim VacationDate As String
VacationDate = InputBox("What Date")
    For Each d In Range("A1:A" & Lastrow)
        For Each c In Range("Calendar")
            If c.Value = VacationDate Then ans = d.Value & "  " & ans: sss = c.Address
        Next
    Next
Range(sss).Value = Range(sss).Value & "  " & ans
End Sub