J
James McKillen
I have a spreadsheet that I check for valid data. I have created an add-in
workbook that has the valid data (named ranges) which is updated from an
Access database query. On the spreadsheet I am checking for valid data, I
created a named reference to the named ranges in the add-in workbook so I
can use conditional formatting to flag invalid data with a red background.
This works perfectly for account numbers, security numbers, and the like,
but when it comes to text entries such as usernames, it flags it as red
whether the data is valid or not. I use VBA code from the add-in workbook
entirely because the spreadsheet I am checking for valid data cannot have
any code associated with it whatsoever. Anyone have any ideas? I believe I
have tried everything, but I am probably wrong! I have formatted both areas
as general and text to no avail. Sample code from the problem area follows:
Option Compare Text
' To add the reference to the named range in the add-in I want to check
against.
Sub AddNames()
ActiveWorkbook.Names.Add Name:="eta_users_ref", RefersToR1C1:="='[ETA2
Verification Data.xla]TRANSACTION_ENTRY_STAFF_ID'!eta_users"
End Sub
Sub ValidUsers()
ActiveSheet.Range("B6").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=COUNTIF(eta_users_ref,R6C2)=0"
Selection.FormatConditions(1).Interior.ColorIndex = 3
End Sub
workbook that has the valid data (named ranges) which is updated from an
Access database query. On the spreadsheet I am checking for valid data, I
created a named reference to the named ranges in the add-in workbook so I
can use conditional formatting to flag invalid data with a red background.
This works perfectly for account numbers, security numbers, and the like,
but when it comes to text entries such as usernames, it flags it as red
whether the data is valid or not. I use VBA code from the add-in workbook
entirely because the spreadsheet I am checking for valid data cannot have
any code associated with it whatsoever. Anyone have any ideas? I believe I
have tried everything, but I am probably wrong! I have formatted both areas
as general and text to no avail. Sample code from the problem area follows:
Option Compare Text
' To add the reference to the named range in the add-in I want to check
against.
Sub AddNames()
ActiveWorkbook.Names.Add Name:="eta_users_ref", RefersToR1C1:="='[ETA2
Verification Data.xla]TRANSACTION_ENTRY_STAFF_ID'!eta_users"
End Sub
Sub ValidUsers()
ActiveSheet.Range("B6").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=COUNTIF(eta_users_ref,R6C2)=0"
Selection.FormatConditions(1).Interior.ColorIndex = 3
End Sub