CODEWITHSUNDEEP
vbaexcel
Haissam
Haissam

Reputation: 113

Change excel background colour on specific text entry

Heading may be a bit misleading, so here goes..

In a spreadsheet I need to monitor Col "I" to test if the number inserted there contains "22822".. it normally is a number like 22822564 or 22722762. these numbers mean something so if 22822xxx is entered the cell just left (in Col "H" has to change and display a yellow background and the Text "Waiting on Processing"

Once the set is processed the user will manually change this value to "Processed" and then the background col has to change back to nothing...

Thus.. User enters 22822564 in I33, then H33 will display "Waiting on Processing" with a yellow background. If the content of H33 is manually changed then remove the background colour.

I have so far done this and am stuck..

  1. the find.. does not seem to work, most probably because i did not tell it to find a part but an exact match, don´t know how to.. hitting a blank here
  2. don´t seem to be able to change the col "H" even with an exact match
  3. have not even gotten as far as the part where a manual entry is made into "Hx"

Any helpers ?

Sub Worksheet_Change(ByVal Target As Range)
   Dim WatchRange As Range
   Dim IntersectRange As Range
   Dim FillRange As Range
   Dim IntersectFillRange As Range
   Set WatchRange = Range("I1:I1110")
   Set FillRange = Range("H1:H1110")
   Set IntersectRange = Intersect(Target, WatchRange)
   Set IntersectFillRange = Intersect(FillRange, WatchRange)
   If IntersectRange Is Nothing Then
    'Do Nothing
   Else
    On Error Resume Next
    check = Application.WorksheetFunction.Search("22822", IntersectRange)
    On Error GoTo 0

    If check = False Then
        MsgBox "String does not contain 22822"
    Else:
        MsgBox "Found it !!!"
        IntersectFillRange.Interior.Color = RGB(200, 160, 35)
    End If


   End If
   End Sub

Upvotes: 0

Views: 1563

Answers (1)

Alex P
Alex P

Reputation: 12487

How about this?

Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Set WatchRange = Range("I1:I1110")

    If Not Intersect(Target, WatchRange) Is Nothing Then
        If VBA.Left$(Target, 5) = "22822" Then
            Target.Offset(0, -1) = "Waiting on Processing"
            Target.Offset(0, -1).Interior.Color = RGB(200, 160, 35)
        End If
    End If
End Sub

If you want to remove the color formatting if somebody overwrites "Waiting on Processing" with "Processed" then I'd simply set a conditional format on H1:H1110 which sets the background color to white if the cell value equals "Processed"

Upvotes: 2

Related Questions