Conditional Formatting

R

rvvan

New Member
Joined
Jun 22, 2016
Messages
14
Hello,

I have a chart in sheet 4 (E2:AJ18) that is a list of tote numbers. In sheet 1 I am "adding a tote" to the system and want it to find the tote that was entered in sheet 1 (A:2) and highlight it Red in the chart. and then in sheet 14, after any cell in column J is populated with "printing" I need it to find the tote # in column H and highlight it Green in the chart, and also delete the entire row in sheet 14. Is this possible? Any help would be GREAT!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
rvvan said:
Hello,

I have a chart in sheet 4 (E2:AJ18) that is a list of tote numbers. In sheet 1 I am "adding a tote" to the system and want it to find the tote that was entered in sheet 1 (A:2) and highlight it Red in the chart. and then in sheet 14, after any cell in column J is populated with "printing" I need it to find the tote # in column H and highlight it Green in the chart, and also delete the entire row in sheet 14. Is this possible? Any help would be GREAT!
Click to expand...
Hi again rvvan,

All of this can be done with conditional formatting except for the row deletion part. As 01-770 has said that would require VBA, at which point I would forgo the the conditional formatting thing and replace the whole process with VBA.

Leave it with me to have a think and if nobody else has provided you a solution by the time I get back then we can see if my suggestion is any good.

What will make a difference to how this works will be whether the value in A2 on Sheet1 is manually entered or if it is the result of a formula.

Likewise will you be manually entering "Printing" into column J of Sheet14 or is that the result of a formula?
 
Upvote 0
Fishboy said:
Hi again rvvan,

All of this can be done with conditional formatting except for the row deletion part. As 01-770 has said that would require VBA, at which point I would forgo the the conditional formatting thing and replace the whole process with VBA.

Leave it with me to have a think and if nobody else has provided you a solution by the time I get back then we can see if my suggestion is any good.

What will make a difference to how this works will be whether the value in A2 on Sheet1 is manually entered or if it is the result of a formula.

Likewise will you be manually entering "Printing" into column J of Sheet14 or is that the result of a formula?
Click to expand...

Okay, VBA is fine! and it will be a result of a formula!
 
Upvote 0
rvvan said:
A2 is inputed manually (from a drop down list from data validation)
Click to expand...
OK, so this part is simple enough. Right-click on the tab for Sheet1 and select View Code. In the window that opens just copy / paste in the following:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
' Defines variables
Dim sRange As Range, Rng As Range, FindString As String


' Sets the search range as E2:AJ18 on Sheet4
Set sRange = Sheets("Sheet4").Range("E2:AJ18")


' If the target address is cell A2 then...
If Target.Address = "$A$2" Then
    ' If the target value is not blank then...
    If Target.Value <> "" Then
        ' Update variable FindString with the updated target value
        FindString = Target.Value
        ' With the search range
        With sRange
            ' Set Rng as the cell where the value is found
            Set Rng = .Find(What:=FindString, _
                    After:=.Cells(1), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
            ' If Rng exists then
            If Not Rng Is Nothing Then
                ' Fill the found cell red
                Rng.Interior.ColorIndex = 3
            End If
        End With
    End If
End If
    
End Sub

This should fill the corresponding cell on Sheet4 when you pick a tote number from the drop-down list in A2 of Sheet1. You will need to let me know if the filled cell is supposed to go back to normal when you select a different cell, or whether the fill is to remain. If the fill is going to remain then the above code is fine, otherwise I will need to tweak the code above somewhat.

With regards to column J of Sheet14 you say this is the result of a formula. Are you able to share the formula with me? The reason I ask is because changes made by formulas are not able to trigger a Worksheet_Change event meaning we would either need to A) use a Worksheet_Calculate event instead however this will fire off every time the sheet is recalculated in any way (this can potentially end up being particularly resource intensive as it is in effect almost constantly being triggered over and over again) or B) use the formula to find somewhere else in the workbook being manually changed to trigger it all off and use a Worksheet_Change event on that sheet instead to make the changes occur.
 
Upvote 0
Fishboy said:
OK, so this part is simple enough. Right-click on the tab for Sheet1 and select View Code. In the window that opens just copy / paste in the following:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
' Defines variables
Dim sRange As Range, Rng As Range, FindString As String


' Sets the search range as E2:AJ18 on Sheet4
Set sRange = Sheets("Sheet4").Range("E2:AJ18")


' If the target address is cell A2 then...
If Target.Address = "$A$2" Then
    ' If the target value is not blank then...
    If Target.Value <> "" Then
        ' Update variable FindString with the updated target value
        FindString = Target.Value
        ' With the search range
        With sRange
            ' Set Rng as the cell where the value is found
            Set Rng = .Find(What:=FindString, _
                    After:=.Cells(1), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
            ' If Rng exists then
            If Not Rng Is Nothing Then
                ' Fill the found cell red
                Rng.Interior.ColorIndex = 3
            End If
        End With
    End If
End If
    
End Sub

This should fill the corresponding cell on Sheet4 when you pick a tote number from the drop-down list in A2 of Sheet1. You will need to let me know if the filled cell is supposed to go back to normal when you select a different cell, or whether the fill is to remain. If the fill is going to remain then the above code is fine, otherwise I will need to tweak the code above somewhat.

With regards to column J of Sheet14 you say this is the result of a formula. Are you able to share the formula with me? The reason I ask is because changes made by formulas are not able to trigger a Worksheet_Change event meaning we would either need to A) use a Worksheet_Calculate event instead however this will fire off every time the sheet is recalculated in any way (this can potentially end up being particularly resource intensive as it is in effect almost constantly being triggered over and over again) or B) use the formula to find somewhere else in the workbook being manually changed to trigger it all off and use a Worksheet_Change event on that sheet instead to make the changes occur.
Click to expand...

Actually, column j is not a result of a formula! it is inserted in. And the fill is suppose to remain red until it is changed to green from the result of column j being "Printing"

Question! Any reason why when i try to run the code and it pops up the list of macros i want to run, that the name of the code doesnt show there? I tried changing to a public sub and I still cant find it from the list.
 
Last edited:
Upvote 0
rvvan said:
Actually, column j is not a result of a formula! it is inserted in. And the fill is suppose to remain red until it is changed to green from the result of column j being "Printing"

Question! Any reason why when i try to run the code and it pops up the list of macros i want to run, that the name of the code doesnt show there? I tried changing to a public sub and I still cant find it from the list.
Click to expand...
OK, so Worksheet_Change macros do not appear in the ALT+F8 macro window. So long as you put them in the correct place by right-clicking on the name tab for the sheet being manually changed and select View Code, the macros should work automatically. This means that when you manually change the value in A2 on Sheet4 the code should trigger.

Likewise, with this new code below, if you enter the value "Printing" in a cell in column J of Sheet14 it should also trigger. To apply it you will need to right-click on the tab for Sheet14, select View Code and copy paste in the following:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
' Defines variables
Dim sRange As Range, Rng As Range, FindString As String


' Sets the search range as E2:AJ18 on Sheet4
Set sRange = Sheets("Sheet4").Range("E2:AJ18")


' If the target column is cell J then...
If Target.Column = 10 Then
    ' If the target value is Printing then...
    If UCase(Target.Value) = "PRINTING" Then
        ' If there is a tote number in column H of the target row then...
        If Range("H" & Target.Row).Value <> "" Then
            ' Update variable FindString with the tote number from column H of the target row
            FindString = Range("H" & Target.Row).Value
            ' With the search range
            With sRange
                ' Set Rng as the cell where the value is found
                Set Rng = .Find(What:=FindString, _
                        After:=.Cells(1), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False)
                ' If Rng exists then
                If Not Rng Is Nothing Then
                    ' Fill the found cell green
                    Rng.Interior.ColorIndex = 4
                End If
            End With
        End If
    End If
End If
    
End Sub

Finally just to reiterate and clarify, neither my original macro or the one above can be run from the standard macro window.

You can see this all in action by downloading my test workbook HERE
 
Upvote 0
This is exactly what I needed. Thanks a bunch sir!!
Fishboy said:
OK, so Worksheet_Change macros do not appear in the ALT+F8 macro window. So long as you put them in the correct place by right-clicking on the name tab for the sheet being manually changed and select View Code, the macros should work automatically. This means that when you manually change the value in A2 on Sheet4 the code should trigger.

Likewise, with this new code below, if you enter the value "Printing" in a cell in column J of Sheet14 it should also trigger. To apply it you will need to right-click on the tab for Sheet14, select View Code and copy paste in the following:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
' Defines variables
Dim sRange As Range, Rng As Range, FindString As String


' Sets the search range as E2:AJ18 on Sheet4
Set sRange = Sheets("Sheet4").Range("E2:AJ18")


' If the target column is cell J then...
If Target.Column = 10 Then
    ' If the target value is Printing then...
    If UCase(Target.Value) = "PRINTING" Then
        ' If there is a tote number in column H of the target row then...
        If Range("H" & Target.Row).Value <> "" Then
            ' Update variable FindString with the tote number from column H of the target row
            FindString = Range("H" & Target.Row).Value
            ' With the search range
            With sRange
                ' Set Rng as the cell where the value is found
                Set Rng = .Find(What:=FindString, _
                        After:=.Cells(1), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False)
                ' If Rng exists then
                If Not Rng Is Nothing Then
                    ' Fill the found cell green
                    Rng.Interior.ColorIndex = 4
                End If
            End With
        End If
    End If
End If
    
End Sub

Finally just to reiterate and clarify, neither my original macro or the one above can be run from the standard macro window.

You can see this all in action by downloading my test workbook HERE
Click to expand...
 
Upvote 0
Not sure how to input a screenshot, but anyreason why it would be giving me a runtime error 9 message :out of range? It works on the excel file you made, but it seems to not like my sheet numbers...



Fishboy said:
OK, so Worksheet_Change macros do not appear in the ALT+F8 macro window. So long as you put them in the correct place by right-clicking on the name tab for the sheet being manually changed and select View Code, the macros should work automatically. This means that when you manually change the value in A2 on Sheet4 the code should trigger.

Likewise, with this new code below, if you enter the value "Printing" in a cell in column J of Sheet14 it should also trigger. To apply it you will need to right-click on the tab for Sheet14, select View Code and copy paste in the following:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
' Defines variables
Dim sRange As Range, Rng As Range, FindString As String


' Sets the search range as E2:AJ18 on Sheet4
Set sRange = Sheets("Sheet4").Range("E2:AJ18")


' If the target column is cell J then...
If Target.Column = 10 Then
    ' If the target value is Printing then...
    If UCase(Target.Value) = "PRINTING" Then
        ' If there is a tote number in column H of the target row then...
        If Range("H" & Target.Row).Value <> "" Then
            ' Update variable FindString with the tote number from column H of the target row
            FindString = Range("H" & Target.Row).Value
            ' With the search range
            With sRange
                ' Set Rng as the cell where the value is found
                Set Rng = .Find(What:=FindString, _
                        After:=.Cells(1), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False)
                ' If Rng exists then
                If Not Rng Is Nothing Then
                    ' Fill the found cell green
                    Rng.Interior.ColorIndex = 4
                End If
            End With
        End If
    End If
End If
    
End Sub

Finally just to reiterate and clarify, neither my original macro or the one above can be run from the standard macro window.

You can see this all in action by downloading my test workbook HERE
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Monthly schedule - conditional formatting date range that exists in one cell
Replies
4
Views
340
myall_blues
myall_blues
J
  • Question Question
Conditional Formatting with deleting rows
Replies
1
Views
357
6StringJazzer
6StringJazzer
E
  • Question Question
Specific VBA Function
Replies
0
Views
236
EAlexWolfe
E
C
  • Solved
Conditional Formatting Assistance
2
Replies
16
Views
529
felixstraube
F
O
  • Question Question
Need help with formulas for conditional formatting
Replies
1
Views
406
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,223,248
Messages
6,171,021
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top