Excel VBA If cell Value then

Question

I have this code which works well   Sub colortest   Dim cell As Range For Each cell In Range  Range1   If cell Value    Word1  Then cell Interior Color   XlRgbColor rgbLightGreen ElseIf cell Value    Word2  Then cell Interior Color   XlRgbColor rgbOrange ElseIf cell Value    Word3  Then cell Interior Color   XlRgbColor rgbRed End If Next cell End Sub   My problem is the values work only if the cell contains only that text  But my cells are usually like this   Something  10254  15 15  Word1 Another Word  I only need the Word1  Thank you

User · Accepted Answer

You can determine if as certain word is found in a cell by using

If InStr(cell.Value, "Word1") > 0 Then

If Word1 is found in the string the InStr() function will return the location of the first character of Word1 in the string.

User · Answer

I think it would make more sense to use  Find  function in Excel instead of For Each loop  It works much much faster and it s designed for such actions  Try this    Sub FindSomeCells strSearchQuery As String          Set SearchRange   Worksheets  Sheet1   Range  A1 A100       FindWhat   strSearchQuery     Set FoundCells   FindAll SearchRange  SearchRange                                FindWhat  FindWhat                                LookIn  xlValues                                LookAt  xlWhole                                SearchOrder  xlByColumns                                MatchCase  False                                BeginsWith  vbNullString                                EndsWith  vbNullString                                BeginEndCompare  vbTextCompare      If FoundCells Is Nothing Then         Debug Print  Value Not Found      Else         For Each FoundCell In FoundCells             FoundCell Interior Color   XlRgbColor rgbLightGreen         Next FoundCell     End If  End Sub   That subroutine searches for some string and returns a collections of cells fullfilling your search criteria  Then you can do whatever you want with the cells in that collection  Forgot to add the FindAll function definition   Function FindAll SearchRange As Range                    FindWhat As Variant                   Optional LookIn As XlFindLookIn   xlValues                    Optional LookAt As XlLookAt   xlWhole                    Optional SearchOrder As XlSearchOrder   xlByRows                    Optional MatchCase As Boolean   False                    Optional BeginsWith As String   vbNullString                    Optional EndsWith As String   vbNullString                    Optional BeginEndCompare As VbCompareMethod   vbTextCompare  As Range                                                                                         FindAll   This searches the range specified by SearchRange and returns a Range object   that contains all the cells in which FindWhat was found  The search parameters to   this function have the same meaning and effect as they do with the   Range Find method  If the value was not found  the function return Nothing  If   BeginsWith is not an empty string  only those cells that begin with BeginWith   are included in the result  If EndsWith is not an empty string  only those cells   that end with EndsWith are included in the result  Note that if a cell contains   a single word that matches either BeginsWith or EndsWith  it is included in the   result   If BeginsWith or EndsWith is not an empty string  the LookAt parameter   is automatically changed to xlPart  The tests for BeginsWith and EndsWith may be   case-sensitive by setting BeginEndCompare to vbBinaryCompare  For case-insensitive   comparisons  set BeginEndCompare to vbTextCompare  If this parameter is omitted    it defaults to vbTextCompare  The comparisons for BeginsWith and EndsWith are   in an OR relationship  That is  if both BeginsWith and EndsWith are provided    a match if found if the text begins with BeginsWith OR the text ends with EndsWith                                                                                         Dim FoundCell As Range Dim FirstFound As Range Dim LastCell As Range Dim ResultRange As Range Dim XLookAt As XlLookAt Dim Include As Boolean Dim CompMode As VbCompareMethod Dim Area As Range Dim MaxRow As Long Dim MaxCol As Long Dim BeginB As Boolean Dim EndB As Boolean CompMode   BeginEndCompare If BeginsWith  lt  gt  vbNullString Or EndsWith  lt  gt  vbNullString Then     XLookAt   xlPart Else     XLookAt   LookAt End If   this loop in Areas is to find the last cell   of all the areas  That is  the cell whose row   and column are greater than or equal to any cell   in any Area   For Each Area In SearchRange Areas     With Area         If  Cells  Cells Count  Row  gt  MaxRow Then             MaxRow    Cells  Cells Count  Row         End If         If  Cells  Cells Count  Column  gt  MaxCol Then             MaxCol    Cells  Cells Count  Column         End If     End With Next Area Set LastCell   SearchRange Worksheet Cells MaxRow  MaxCol  On Error GoTo 0 Set FoundCell   SearchRange Find what  FindWhat            after  LastCell            LookIn  LookIn            LookAt  XLookAt            SearchOrder  SearchOrder            MatchCase  MatchCase  If Not FoundCell Is Nothing Then     Set FirstFound   FoundCell     Do Until False   Loop forever  We ll  Exit Do  when necessary          Include   False         If BeginsWith   vbNullString And EndsWith   vbNullString Then             Include   True         Else             If BeginsWith  lt  gt  vbNullString Then                 If StrComp Left FoundCell Text  Len BeginsWith    BeginsWith  BeginEndCompare    0 Then                     Include   True                 End If             End If             If EndsWith  lt  gt  vbNullString Then                 If StrComp Right FoundCell Text  Len EndsWith    EndsWith  BeginEndCompare    0 Then                     Include   True                 End If             End If         End If         If Include   True Then             If ResultRange Is Nothing Then                 Set ResultRange   FoundCell             Else                 Set ResultRange   Application Union ResultRange  FoundCell              End If         End If         Set FoundCell   SearchRange FindNext after  FoundCell          If  FoundCell Is Nothing  Then             Exit Do         End If         If  FoundCell Address   FirstFound Address  Then             Exit Do         End If     Loop End If Set FindAll   ResultRange End Function

User · Answer

You can use the Like operator with a wildcard to determine whether a given substring exists in a string  for example   If cell Value Like   Word1   Then      ElseIf cell Value Like   Word2   Then      End If   In this example the   character in   Word1   is a wildcard character which matches zero or more characters   NOTE  The Like operator is case-sensitive  so  Word1  Like  word1  is false  more information can be found on this MSDN page

[vba] Excel VBA If cell.Value =... then

Examples related to vba

Examples related to excel