select from range

Trevor3007

Active Member
Joined
Jan 26, 2017
Messages
494
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
 
Last edited:

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,450
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")

Code:
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

Active Member
Joined
Jan 26, 2017
Messages
494
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")

Code:
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
Code:
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
 
Last edited by a moderator:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,402
Office Version
365
Platform
Windows
The VBA project in that file is password protected.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,450
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

Active Member
Joined
Jan 26, 2017
Messages
494
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
 

Forum statistics

Threads
1,085,843
Messages
5,386,297
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top