CONDITIONAL FORMATTING (OFFICE 2016)

Solved/Closed
lcarolus20
Posts
4
Registration date
Wednesday July 21, 2021
Status
Member
Last seen
July 22, 2021
- Jul 21, 2021 at 05:40 AM
vcoolio
Posts
1359
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 25, 2022
- Jul 22, 2021 at 01:31 AM
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
Related:

6 replies

vcoolio
Posts
1359
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 25, 2022
250
Updated on Jul 21, 2021 at 07:21 AM
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
vcoolio
Posts
1359
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 25, 2022
250
Jul 21, 2021 at 06:06 AM
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.
0
lcarolus20
Posts
4
Registration date
Wednesday July 21, 2021
Status
Member
Last seen
July 22, 2021

Updated on Jul 21, 2021 at 06:36 AM
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
0
lcarolus20
Posts
4
Registration date
Wednesday July 21, 2021
Status
Member
Last seen
July 22, 2021

Updated on Jul 21, 2021 at 08:55 AM
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
0

Didn't find the answer you are looking for?

Ask a question
vcoolio
Posts
1359
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 25, 2022
250
Updated on Jul 21, 2021 at 08:19 PM
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.
0
lcarolus20
Posts
4
Registration date
Wednesday July 21, 2021
Status
Member
Last seen
July 22, 2021

Jul 22, 2021 at 01:09 AM
Gm Sir

Yes man..The second code works perfectly :)
0
vcoolio
Posts
1359
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 25, 2022
250
Jul 22, 2021 at 01:31 AM
Excellent! Thumbs up all round!

Cheerio,
vcoolio.
0