Change Color If Selected Cells Edited

[barim]

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.

[Leith Ross]

Hello barim,

I changed the Conditional Formatting rules in the attached workbook. Have a look and let me know if this what you wanted.

[barim]

Leith Ross, it's so good to hear from you. :hi:

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.

[barim]

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

[barim]

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.

[barim]

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.