Conditional formatting help needed

[zombiemaster]

I have random entries in Column "I" that are taken from a list of 22 categories on another tab called "Time Standards" in Column "C". What we need to do is if someone enters something incorrectly into Column I that is NOT in the Time Standards list, we want that cell to turn a color. I tried using Conditional Formatting for all of Column I on our input tab, but it didn't work, but I think it might have been on the right track...any thoughts? I'm sure this is easy for someone else but my brain just isn't up to it.

Basically:

1. Items get entered/pasted into Column I daily.
2. We have a hidden tab called Time Standards with a list of approved words in column C that we should be using, and nothing else.
3. If someone types "Cart" in column I but it should have been spelled "Cars" as it is listed on the Time Standards tab, I want that cell to turn orange.

Any help is appreciated!

-=ZM=-

 

[KillerOfGiants]

Try this for conditional formatting. Use the "Use a formula to determine which cells to format"

=COUNTIF(Sheet2!$A$1:$A$3,$C3)=1

Where Sheet2!$A$1:$A$3 is the range on your Time Standards, and $C3 is the cell being evaluated.

Another way to do it is to define the Time Standards that are acceptable as a list, and then use Data Validation set to list and equal to the named list range.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet where you enter random entries in Column "I" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your entries in column I.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 9 Then Exit Sub
    Application.ScreenUpdating = False
    Dim rng As Range, fnd As Range
    For Each rng In Target
        Set fnd = Sheets("Time Standards").Range("C:C").Find(rng.Value, LookIn:=xlValues, lookat:=xlWhole)
        If fnd Is Nothing Then
            rng.Interior.ColorIndex = 44
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[rlv01]

If you can create a True/False formula for a condition, then you can use that formula to drive conditional formatting. So for example, say you were to create a 'helper' column as col H and then devise a formula to look at the col I value and produce a TRUE or FALSE result depending on whether the col I value was found in your 'Standards' list, that same formula could be used to drive conditional formatting in col I (instead of using a helper column). As far as what the formula should be? You know your workbook better than I do, if you already have some sort of formula/function to access that "Time Standards" list, that might be a place to start.

 

[zombiemaster]

Try this for conditional formatting. Use the "Use a formula to determine which cells to format"

=COUNTIF(Sheet2!$A$1:$A$3,$C3)=1

Where Sheet2!$A$1:$A$3 is the range on your Time Standards, and $C3 is the cell being evaluated.

Another way to do it is to define the Time Standards that are acceptable as a list, and then use Data Validation set to list and equal to the named list range.

So I modified your formula to match my sheets:

=COUNTIF('Time Standards'!$C$3:$C$25,$I7)

Unfortunately, EVERYTHING in the column highlighted orange, instead of just the items that didn't match the list on the Time Standards tab. Any thoughts on what may have happened?

Thanks,
-=ZM=-

 

[zombiemaster]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet where you enter random entries in Column "I" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your entries in column I.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 9 Then Exit Sub
    Application.ScreenUpdating = False
    Dim rng As Range, fnd As Range
    For Each rng In Target
        Set fnd = Sheets("Time Standards").Range("C:C").Find(rng.Value, LookIn:=xlValues, lookat:=xlWhole)
        If fnd Is Nothing Then
            rng.Interior.ColorIndex = 44
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Thanks for trying but this isn't a sheet that we're looking to create a macro for - they just want Conditional Formatting to fix the issue. Thanks, though!

-=ZM=-

 

[Joe4]

Please post some sample data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

One thing that does concern me is this line here:

Items get entered/pasted into Column I daily.

If you are copy and pasting values over from somewhere else, it will copy the formatting from your source cell too, meaning you will overwrite the Conditional Formatting rules you have set up!
So Conditional Formatting probably will NOT work you for if you are copy/pasting data into here. That is where VBA may be required to do what you want and keeping the ability to copy/paste.

 

[Joe4]

Sorry, looks like I may have accidentally locked this thread when replying to it. It should be unlocked now.

 

[zombiemaster]

Please post some sample data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

One thing that does concern me is this line here:

If you are copy and pasting values over from somewhere else, it will copy the formatting from your source cell too, meaning you will overwrite the Conditional Formatting rules you have set up!
So Conditional Formatting probably will NOT work you for if you are copy/pasting data into here. That is where VBA may be required to do what you want and keeping the ability to copy/paste.

Thanks for reminding me that copy/pasting will overwrite the formatting. Once I had that in my head, I was able to figure out a way to do what needed to be done using a combination of the Conditional Formatting and a formula in another cell.

Thank you!

-=ZM=-

 

[Joe4]

You are welcome.
Glad we were able to help!