Previous Topic Next Topic

CONDITIONAL FORMATTING (OFFICE 2016)

Solved/Closed
lcarolus20 Posts 4 Registration date Wednesday 21 July 2021 Status Member Last seen 22 July 2021 - 21 Jul 2021 à 05:40
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 - 22 Jul 2021 à 01:31
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 responses

Réponse 1 / 6
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 262
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.
Réponse 2 / 6
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 262
21 Jul 2021 à 06:06
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.
Réponse 3 / 6
lcarolus20 Posts 4 Registration date Wednesday 21 July 2021 Status Member Last seen 22 July 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
Réponse 4 / 6
lcarolus20 Posts 4 Registration date Wednesday 21 July 2021 Status Member Last seen 22 July 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
Réponse 5 / 6
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 262
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.
lcarolus20 Posts 4 Registration date Wednesday 21 July 2021 Status Member Last seen 22 July 2021
22 Jul 2021 à 01:09
Gm Sir

Yes man..The second code works perfectly :)
Réponse 6 / 6
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 262
22 Jul 2021 à 01:31
Excellent! Thumbs up all round!

Cheerio,
vcoolio.