select from range

[Trevor3007]

good day,


For Each rng In Range("h4:h200")
Select Case rng.Value
Case "Build Completed"
With Range("A" & rng.Row).Resize(1, 22)
.Interior.ColorIndex = 4
.Font.Bold = True


the code highlighted in red is a triggered word. However, its a rather long winded way to change the word should the end user want to?
Is it possible to use a cell ref to do this?

"Build Completed" is part of drop down wihch is in the worktab name "Lists!A3" .

Many thanks in advance & KR
Trevor3007

 

[James006]

Hello,

Within your macro ... the instruction just above ...

Select Case rng.Value

does exactly what you are asking for ... since it refers to the cell value ...

HTH

 

[My Aswer Is This]

If your looking for a easy way to change the search for value without modifying the code.
Use this:
Enter your search for value in Range("A1")

Sub My_Sub()
'Modified  12/20/2018  4:14:47 AM  EST
Dim ans As String
ans = Range("A1").Value
For Each Rng In Range("h4:h200")
    Select Case Rng.Value
    Case ans
        With Range("A" & Rng.Row).Resize(1, 22)
            .Interior.ColorIndex = 4
            .Font.Bold = True
        End With
    End Select
Next
End Sub

 

[Trevor3007]

If your looking for a easy way to change the search for value without modifying the code.
Use this:
Enter your search for value in Range("A1")

Sub My_Sub()
'Modified  12/20/2018  4:14:47 AM  EST
Dim ans As String
ans = Range("A1").Value
For Each Rng In Range("h4:h200")
    Select Case Rng.Value
    Case ans
        With Range("A" & Rng.Row).Resize(1, 22)
            .Interior.ColorIndex = 4
            .Font.Bold = True
        End With
    End Select
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
 
Application.ScreenUpdating = False
    Dim rng As Range
      
    For Each rng In Range("h4:h200")
        Select Case rng.Value
            Case "[COLOR=#ff0000]Build Completed[/COLOR]"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 4
                    .Font.Bold = True
                End With
            Case "[COLOR=#ff0000]Swapped-Out[/COLOR]"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 22
                    .Font.Bold = True
                End With
            Case "[COLOR=#ff0000]Build Started[/COLOR]"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 6
                    .Font.Bold = True
                End With
            Case "[COLOR=#ff0000]Device Not Received[/COLOR]"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 28
                    .Font.Bold = True
                End With
            Case "[COLOR=#ff0000]Emailed Requested For SCCM Check[/COLOR]"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 38
                    .Font.Bold = True
                End With
            Case "[COLOR=#ff0000]Desktop UAD - On Hold ATM[/COLOR]"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 44
                    .Font.Bold = True
                End With
            Case [COLOR=#ff0000]"Device With Build Engineer[/COLOR]"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 40
                    .Font.Bold = False
                End With
            Case ""
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = xlNone
                    .Font.Bold = False
                End With
        End Select
    Next rng
    Application.ScreenUpdating = True
   
    End Sub

All the ‘red text’ are on another tab called ‘lists’. I use ‘data validation’ from the applicable drop down in range h4-h200 on the daily date worksheet .
The following is what is in the ‘data validation’ located on the lists worktab a2-a9 (a2 is blank )

Build Completed -
Build Started
Device Not Received
Device With Build Engineer
Emailed Requested For SCCM Check
Swapped-Out
Desktop UAD - On Hold ATM

So , can you sort so that the ‘ red text ' so the VBA would look to that range for the applicable value should I need to change any?

I could not get your code to work BTW :[ sorry



MTIA
Trevor3007

 

[Trevor3007]

https://1drv.ms/x/s!AvGGXsEtXRpdhLQJ6fT_WkNXeNi3qQ

please see my workbook vla the link above

 

[Norie]

The VBA project in that file is password protected.

 

[Trevor3007]

The VBA project in that file is password protected.

sorry its C@de2663

 

[Trevor3007]

The VBA project in that file is password protected.

hello Norrie,

did you mange to access the code?

KR
Trevor3007

 

[My Aswer Is This]

The reason my script did not work was because I based it on only 7 lines of code you provide.
And you never mentioned it was a sheet change event script which I now see in one of your later postings.

I will move on and see what Norie can do for you.

 

[Trevor3007]

The reason my script did not work was because I based it on only 7 lines of code you provide.
And you never mentioned it was a sheet change event script which I now see in one of your later postings.

I will move on and see what Norie can do for you.

many thanks,

....and sorry for the confusion. Hopefully Norrie will sort ?

KR
Trevor3007