CONDITIONAL FORMATTING (OFFICE 2016) [Solved]

Report
Posts
4
Registration date
Wednesday July 21, 2021
Status
Member
Last seen
July 22, 2021
-
Posts
1309
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 22, 2021
-
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

6 replies

Posts
1309
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 22, 2021
232
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.
2
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Posts
1309
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 22, 2021
232
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.
Posts
4
Registration date
Wednesday July 21, 2021
Status
Member
Last seen
July 22, 2021

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
Posts
4
Registration date
Wednesday July 21, 2021
Status
Member
Last seen
July 22, 2021

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
Posts
1309
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 22, 2021
232
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.
Posts
4
Registration date
Wednesday July 21, 2021
Status
Member
Last seen
July 22, 2021

Gm Sir

Yes man..The second code works perfectly :)
Posts
1309
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 22, 2021
232
Excellent! Thumbs up all round!

Cheerio,
vcoolio.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!