CONDITIONAL FORMATTING (OFFICE 2016)Solved/Closed

Question

Hi Guys

I want Excel to highlight  cells(in a column) based on the   value(text) I select from a drop-down list in another column.

So each time I select this value "complete" from the drop-down list I want excel to highlight another cell in another column(but still in the same row) with a certain color.

My drop down list runs from $C$3-$C$33
The column runs from $H$3-$H$33

vcoolio · Accepted Answer

Hello Icarolus,

This will set the colour back to normal (default=no colour) should anything but "Complete" be selected:-

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

If Intersect(Target, Range("C3:C33")) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        If Target.Value = vbNullString Then Exit Sub

If Target.Value = "Complete" Then
              Target.Offset(, 5).Interior.ColorIndex = 6
              Else
              Target.Offset(, 5).Interior.ColorIndex = xlNone
        End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

If I change from "Complete" to "Not Started" the color remains on the color "yellow".

Isn't this contradictory to this:

Should I change the value from "Complete" to say "Not Started" or "In progress" in the drop down the color should go to default value.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Icarolus,

A simple worksheet_change event code will do this for you.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

If Intersect(Target, Range("C3:C33")) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        If Target.Value = vbNullString Then Exit Sub

If Target.Value = "Complete" Then
              Target.Offset(, 5).Interior.ColorIndex = 6
        End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

This code will high-light a cell in Column H in yellow (same row) within your specified range once the criteria "Complete" is selected from the drop downs in Column C.

To implement this code:-

- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.

lcarolus20 · Answer

Hi buddy

Thanks man...but I should have mentioned this in my post...I have 3 values-"Complete", "In Progress" and "Not Started". 

Should I change the value from "Complete" to say "Not Started" or "In progress" in the drop down the color should go to default value.

If I change from "Complete" to "Not Started" the color remains on the color "yellow".This is what is currently happening

lcarolus20 · Answer

Your solution works perfectly..Thanks man

(If I change from "Complete" to "Not Started" the color remains on the color "yellow".) I said this to tell you what excel is doing currently.

I wont say its contradictory.. (example: If I change from "Not Started"("Not Started" does have a default color of white)  to "Complete" the color of the cell change to yellow right...Now the thing  is when I go to the drop down to change the cell value to  "Not Started"  that I have change previously from "Not Started" to "Complete" the cell color is suppose to change back to white

I hope you understand  :) ..Thank you for help my friend

vcoolio · Answer

Hello Icarolus,
Your solution works perfectly..Thanks man

You're welcome.
Does this mean that the second code I supplied does the task for you?

Cheerio,
vcoolio.

vcoolio · Answer

Excellent! Thumbs up all round!

Cheerio,
vcoolio.

CONDITIONAL FORMATTING (OFFICE 2016)

6 responses

Similar discussions

Subscribe To Our Newsletter!