Consulting

Results 1 to 6 of 6

Thread: Change Color If Selected Cells Edited

  1. #1
    VBAX Regular
    Joined
    Jan 2016
    Posts
    55
    Location

    Change Color If Selected Cells Edited

    Hello,

    I would like to see if there is any function or macro that can do this. I have conditional formatting set up based on drop down selection which highlights cells that should be edited. In my workbook that I am attaching I pointed to those rows.

    1. For the "Region1" selection it should be applied OR which means either of those cells edited should both change to whatever color. If Cell E6 is edited or cell F6 both should change color.

    2. For the "Unknown Region" all cells have to be edited in order to change color, so here should be applied AND.

    Appreciate any help this.
    Thank you.
    Attached Files Attached Files

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello barim,

    I changed the Conditional Formatting rules in the attached workbook. Have a look and let me know if this what you wanted.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    VBAX Regular
    Joined
    Jan 2016
    Posts
    55
    Location
    Leith Ross, it's so good to hear from you.

    For the "Unknown region", the purpose is to make a new entry, so when you right click and insert a new row, from the drop down you select "Unknown region" and those cells should be highlighted meaning this is what you need to fill. Once they are edited or filled it should flip to a different color. I noticed that cells are highlighted only when you fill them and not when they are blank. Is this possible to do with conditional formatting?

    For the "Region1" it works, but it should flip color once edited. I am experimenting with worksheet change event. This is what I've got so far:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 3 Then
            Target.Interior.ColorIndex = 27
            End If
    End Sub
    It really changes the color but only on no fill cell not those that are already filled with conditional formatting. Is it possible to use worksheet change events in conjunction with conditional formatting?

    Leith Ross thank you so much for your help on this.

  4. #4
    VBAX Regular
    Joined
    Jan 2016
    Posts
    55
    Location
    I am trying to have one master Worksheet change macro that will call the other macros. If anywhere in column A there is a value "Unknown Region" it should trigger Macro1. Macro1 should insert a row and highlight cells C through F. Once when made entries it should change to a different color. Is this possible to do?

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("A2")) Is Nothing Then
            Select Case Range("A2")
                Case "Unknown Region": Macro1
            End Select
        End If
        End Sub
    Sub Macro1()
    Dim LastRow As Long
    Dim i As Integer
    
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        For i = 2 To LastRow
            If Cells(i, "A").Value = "Unknown Region" Then
                Rows.Insert Shift:=xlShiftDown
                End If
    End Sub

  5. #5
    VBAX Regular
    Joined
    Jan 2016
    Posts
    55
    Location
    I think I will have to give up on drop downs since I don't know how to manipulate them.

    I have this piece of code that will color cell if edited.

    Private Sub Worksheet_Change(ByVal Target As range)
    
    
        If Target.Column = 4 Then
        Target.Interior.ColorIndex = 43
            Else
            If Target.Column = 5 Then
                Target.Interior.ColorIndex = 44
                End If
        If Target.Column = 6 Then
            Target.Interior.ColorIndex = 47
            End If
            End If
          
        End Sub
    Now, I need help in printing text values in column A based on what color is in what column. For example, if it's cell in 4th column colored only then it should print "SKU" in the column A. If there are changes in columns 5 and 6 it should print "Description Change" etc. Also, I would like to protect column A against any possibility to type in except macro would print values based on colors in different columns. I am also attaching sample workbook.
    Thanks in advance.
    Attached Files Attached Files

  6. #6
    VBAX Regular
    Joined
    Jan 2016
    Posts
    55
    Location
    I think I found a solution to this, but freezes my worksheet. Here is the code:

    Private Sub Worksheet_Change(ByVal Target As range)
            
            If Target.Column = 4 Then
            Target.Interior.ColorIndex = 43
                
            Else
            If Target.Column = 5 Then
                Target.Interior.ColorIndex = 44
                End If
            If Target.Column = 6 Then
            Target.Interior.ColorIndex = 47
            End If
            End If
           
        Dim LR As Long, I As Long
        
        LR = range("D" & Rows.Count).End(xlDown).Row
        For I = 2 To LR
            If range("D" & I).Interior.ColorIndex = 43 Then
                range("A" & I).Value = "SKU"
         End If
        Next I
                   
        End Sub
    Why is it freezing my worksheet and how to prevent it? Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •