Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

On cell change, Can you please write me a vba code that will highlight all the cells in…

  1. User

    On cell change, Can you please write me a vba code that will highlight all the cells in the range C2:Y22 where the first three letters of the text value within the cells are identical.

  2. Assistant

    Sure, below is a VBA code that will highlight the cells in the range C2:Y22 if their first three letters are identical:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng As Range
        Dim cell As Range
        
        ' Define the range to check
        Set rng = Range("C2:Y22")
        
        ' Check if the changed cells are within the specified range
        If Not Intersect(Target, rng) Is Nothing Then
            Application.EnableEvents = False
            For Each cell In Intersect(Target, rng)
                ' Highlight cell if first three letters are identical
                If Left(cell.Value, 3) = Left(Target.Value, 3) Then
                    cell.Interior.Color = RGB(255, 255, 0) ' Change the highlight color here (currently set to yellow)
                Else
                    cell.Interior.ColorIndex = xlNone ' Remove existing highlight if first three letters are not identical
                End If
            Next cell
            Application.EnableEvents = True
        End If
    End Sub
    

    To use this code, follow these steps:

    1. Press ALT + F11 to open the VBA editor in Excel.
    2. Double-click on the worksheet name where you want the code to run (e.g., Sheet1).
    3. Paste the above code into the code window.
    4. Close the VBA editor.
    5. Now, whenever you change any cell within the range C2:Y22, the code will check if the first three letters are identical and highlight the cells accordingly.
  3. User

    whichever value I enter into a cell within the range, the cell is highlighted even if the first 3 letters do not match any other cell within the range. Also when I delete the value, the cell interior colour is not removed

  4. Assistant

    Apologies for the confusion. Here's an updated version of the code that addresses those issues:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng As Range
        Dim cell As Range
        
        ' Define the range to check
        Set rng = Range("C2:Y22")
        
        ' Check if the changed cells are within the specified range
        If Not Intersect(Target, rng) Is Nothing Then
            Application.EnableEvents = False
            For Each cell In Intersect(Target, rng)
                If Len(cell.Value) >= 3 Then ' Only check if the cell value has at least 3 characters
                    ' Highlight cell if first three letters are identical
                    If Left(cell.Value, 3) = Left(Target.Value, 3) Then
                        cell.Interior.Color = RGB(255, 255, 0) ' Change the highlight color here (currently set to yellow)
                    Else
                        cell.Interior.ColorIndex = xlNone ' Remove existing highlight if first three letters are not identical
                    End If
                Else
                    cell.Interior.ColorIndex = xlNone ' Remove existing highlight if cell value is less than 3 characters
                End If
            Next cell
            Application.EnableEvents = True
        End If
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim rng As Range
        Dim cell As Range
        
        ' Define the range to check
        Set rng = Range("C2:Y22")
        
        ' Check if any cell in the range has no value, remove the interior color
        For Each cell In rng
            If cell.Value = "" Then
                cell.Interior.ColorIndex = xlNone
            End If
        Next cell
    End Sub
    

    Make sure to replace the existing code in the worksheet's VBA module with the updated code above. Now, the code will only highlight the cells if their first three letters are identical after entering a value, and it will remove the highlighting if the cell value is deleted.

  5. User

    The following code is not highlighting cells within the range C2:Y22 where the first three letters of the text values within the cells are identical: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cell As Range

    ' Define the range to check
    Set rng = Range("C2:Y22")
    
    ' Check if the changed cells are within the specified range
    If Not Intersect(Target, rng) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Intersect(Target, rng)
            If Len(cell.Value) >= 3 Then ' Only check if the cell value has at least 3 characters
                ' Highlight cell if first three letters are identical
                If Left(cell.Value, 3) = Left(Target.Value, 3) Then
                    cell.Interior.Color = RGB(255, 255, 0) ' Change the highlight color here (currently set to yellow)
                Else
                    cell.Interior.ColorIndex = xlNone ' Remove existing highlight if first three letters are not identical
                End If
            Else
                cell.Interior.ColorIndex = xlNone ' Remove existing highlight if cell value is less than 3 characters
            End If
        Next cell
        Application.EnableEvents = True
    End If
    

    End Sub

  6. Assistant

    Apologies for the confusion. There is an issue with the code where it compares the changed cell with each cell in the range, instead of comparing it with all the cells in the range. Here is the updated code that will highlight the cells within the range C2:Y22 where the first three letters of the text values within the cells are identical:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng As Range
        Dim cell As Range
        Dim checkValue As String
        
        ' Define the range to check
        Set rng = Range("C2:Y22")
        
        ' Check if the changed cells are within the specified range
        If Not Intersect(Target, rng) Is Nothing Then
            Application.EnableEvents = False
            
            For Each cell In rng
                checkValue = cell.Value
                
                If Len(checkValue) >= 3 Then ' Only check if the cell value has at least 3 characters
                    ' Highlight cell if first three letters are identical
                    If Left(checkValue, 3) = Left(Target.Value, 3) Then
                        cell.Interior.Color = RGB(255, 255, 0) ' Change the highlight color here (currently set to yellow)
                    Else
                        cell.Interior.ColorIndex = xlNone ' Remove existing highlight if first three letters are not identical
                    End If
                Else
                    cell.Interior.ColorIndex = xlNone ' Remove existing highlight if cell value is less than 3 characters
                End If
            Next cell
            
            Application.EnableEvents = True
        End If
    End Sub
    

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.