Feb 16 2021 02:43 AM
Hi,
I am somewhat of a novice in Excel but I'm working on a project for work.
I am trying to use a formula to conditional format a row when text is found in a cell, however the text although will always be the same format will be any combination of letters/number...
i.e. AB1 to CD2, or AD2 to CB1, or ZG3 to YH7... will always have two letters and one number but could be any letters and numbers. It could also have leading/trailing text i.e. From AB1 to CD2, or AB1 to CD2 transfer
Selecting cells A6 to L6 as this is the range I am working with the data I am looking for in L6, I go in to conditional format and enter this formula...
=ISNUMBER(SEARCH("??# to ??#",$L6)) as I figured ?? would look for 2 letters and # the one number.
I then format cells to highlight red.
But this formula isn't working, any ideas how I could do this?
Thank you
Feb 16 2021 03:04 AM
SolutionSEARCH accepts * and ? as wildcards, but not #.
If you use Excel on Windows, you can use a custom VBA function:
Function Check(s As String) As Boolean
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Pattern = "[A-Za-z]{2}[0-9] to [A-Za-z]{2}[0-9]"
Check = re.Test(s)
End Function
Let's say you want to apply conditional formatting to cells in column B, starting in B2.
Use the following formula in your conditional formatting rule:
=Check(B2)
Don't forget to save the workbook as a macro-enabled workbook, and make sure that you allow macros when you open it.
Feb 16 2021 02:27 PM
Works perfectly thank you,
I thought VBA was the way to go as used it in another project but still very much a novice, was not aware of conditional format to VBA linking.
Managed to adapt it for different criteria so thank you again
Feb 16 2021 03:04 AM
SolutionSEARCH accepts * and ? as wildcards, but not #.
If you use Excel on Windows, you can use a custom VBA function:
Function Check(s As String) As Boolean
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Pattern = "[A-Za-z]{2}[0-9] to [A-Za-z]{2}[0-9]"
Check = re.Test(s)
End Function
Let's say you want to apply conditional formatting to cells in column B, starting in B2.
Use the following formula in your conditional formatting rule:
=Check(B2)
Don't forget to save the workbook as a macro-enabled workbook, and make sure that you allow macros when you open it.