Need help with CASE function

[PierreT]

Hello,

I have a simple excel sheet with all of 2017 calendar dates on it from A5:A369. When I open the excel sheet, it finds today's date in column A and bolds the cell, increases the size and borders it.

1. What I would like to add to the current code is: once it finds the date, I would like to apply all the changes that occur in Column A (bold, size and border) through Column F instead of just one column. I can't seem to figure out how.

2. Finally, if within the row there is a specific word in column D, I would like the column A:F of that row to be highlighted a certain color.

here is what I have so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In ActiveSheet.Range("A1:A400")
    If cell.Value = [Today()] Then
       cell.Borders(xlDiagonalDown).LineStyle = xlNone
       cell.Borders(xlDiagonalUp).LineStyle = xlNone
    
    With cell.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    
    With cell.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    
    With cell.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With cell.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    
    With cell.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    
    With cell.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    
    cell.Font.Size = 14
    cell.Font.Bold = True
    
    Dim squadron As String
    
    Select Case squadron
    
    Case "Test1"
    EntireRow.Interior.Color = 8
    Case "Test2"
    EntireRow.Interior.Color = 9
    End Select
End If
Next
End Sub

Any help would be much appreciated, Thanks

 

[Scott Huish]

You don't need all that code, and VBA has a function to return the current date. In your code, you were not giving squadron a value, therefore nothing to test. I think this is what you are looking for:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
For Each cell In ActiveSheet.Range("A1:A400")
    If cell.Value = Date Then
        Set c = cell.Resize(, 6)
        c.Borders(xlDiagonalDown).LineStyle = xlNone
        c.Borders(xlDiagonalUp).LineStyle = xlNone
        c.Borders.Weight = xlThick
        
    c.Font.Size = 14
    c.Font.Bold = True
    
    Select Case UCase(cell.Offset(, 3))
        Case "TEST1"
            cell.EntireRow.Interior.ColorIndex = 8
        Case "TEST2"
            cell.EntireRow.Interior.ColorIndex = 9
    End Select
    End If
Next
End Sub

 

[My Aswer Is This]

I think you should use this script.
It will run when the sheet is activated.
With your solution the script will run any time any range is changed on your sheet.

And you did not say if you wanted the previous days ranges changed back to normal

For example if todays row is formatted the way you want then when tomorrow comes both rows would be formatted


Try this:

Private Sub Worksheet_Activate()
Dim squadron As String
For Each Cell In ActiveSheet.Range("A1:A400")
    If Cell.Value = Date Then
With Cell.Resize(1, 6)
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThick
        .Font.Name = "Calibri"
        .Font.Size = 14
        .Font.ThemeFont = xlThemeFontMinor
        .Font.Bold = True
squadron = Cell.Offset(0, 3).Value
    Select Case squadron
    
    Case "Test1"
    .Interior.ColorIndex = 8
    Case "Test2"
    .Interior.ColorIndex = 9
    End Select
End With
End If
Next
End Sub

 

[PierreT]

Thanks Scott,

it works like a charm. If I wanted to change the "highlighting" the entire row (cell.EntireRow.Interior.ColorIndex = 8) how could I make it stop at column F?

Thanks again.

 

[My Aswer Is This]

My script only colored A to F.
But I guess my script was not what you wanted for some reason.

Thanks Scott,

it works like a charm. If I wanted to change the "highlighting" the entire row (cell.EntireRow.Interior.ColorIndex = 8) how could I make it stop at column F?

Thanks again.

 

[PierreT]

Thanks Scott,

it works like a charm. If I wanted to change the "highlighting" the entire row (cell.EntireRow.Interior.ColorIndex = 8) how could I make it stop at column F?

Thanks again.

Never mind, I found it. Thanks

 

[PierreT]

My Aswer Is this,

you are right, I did not specified if I wanted the text the go back to normal. Here is what I have so far:

Dim c As Range
For Each cell In ActiveSheet.Range("A1:A400")
    If cell.Value = Date Then
        Set c = cell.Resize(, 8)
        c.Borders(xlDiagonalDown).LineStyle = xlNone
        c.Borders(xlDiagonalUp).LineStyle = xlNone
        c.Borders.Weight = xlThick
        c.RowHeight = 25
        
    c.Font.Size = 14
    c.Font.Bold = True
    
    Select Case UCase(cell.Offset(, 3))
        Case "1"
            cell.Resize(, 4).Interior.ColorIndex = 3
        Case "2"
            cell.Resize(, 4).Interior.ColorIndex = 8
        Case "3"
            cell.Resize(, 4).Interior.ColorIndex = 6
        Case "4"
            cell.Resize(, 4).Interior.ColorIndex = 7
        Case "SP"
            cell.Resize(, 4).Interior.ColorIndex = 43
        Case "HQ"
            cell.Resize(, 4).Interior.ColorIndex = 46
            
    End Select
    End If
Next

will this return text to normal upon date change?

Sorry, I'm trying to learn at the same time.

 

[My Aswer Is This]

I'm not really sure.
I provided a script which you never responded to.
Scott provided a script you said worked perfect but then you said you do not want entire row colored.

But now your showing a script you have put together and are asking questions about it.

If you only want the row with Todays date colored and set to Bold and etc. etc. all other rows set back to normal we would need to know what normal is?

Could we assume that Cells in column "H" for example are "Normal"
If not how do we know what normal is?

And we could reset all cells in columns A to F for example back to normal except for row with todays date

 

[PierreT]

Well, I'm here to learn. Scott's script fitted my needs and worked as intended. As I'm learning more and more and understanding scripts I made some adjustments to improve it. I did not think about restoring the line after it moves to the next one and so forth.
The normal is, Calibri, 11, with thin border.
If some cells already have a fill in them, how do we bring them back to the same color once the day is passed?
Thanks

 

[My Aswer Is This]

I'm glad you liked Scott's script. I'm not really good at modifying others scripts.
So I'm sure he will be back and will help you make changes you want:

One thing is when you use this line of code it means your script will run any time you select any cell on your sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Which I'm not sure why you would want that to happen:

I'll continue to Monitor this thread.