Hi all,
Is anyone aware of any code or process limitations for conditional formatting in excel? I'm trying to use an Index(match(index(isnumber))) function in conditional formatting. My equations run as expected when I run them in a worksheet (i get the appropriate 1 or 0), however, it only conditionally formats the first 48 rows or so when i try to run it in conditional formatting manager.
The code was working when I was using Index(match) with a wildcard "*"&value&"*", however i was limited by the 255 character search max so i had to edit the code. I have about 270 rows of data in my current workbook, but i'll need the code to work for up to 5000.
I'm trying to conditionally format cells red when this equation is confirmed:
=IF(AND(INDEX('PFL Data'!F$3:F$5001,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(B2, 'PFL Data'!C$3:C$5001)),0),0))<>'GIS Transmission Pipe Export'!D2,(NOT(OR('GIS Transmission Pipe Export'!D2=0,'GIS Transmission Pipe Export'!D2="Unknown",'GIS Transmission Pipe Export'!D2=""))),NOT(OR(INDEX('PFL Data'!F$3:F$5001,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(B2, 'PFL Data'!C$3:C$5001)),0),0))=0,(INDEX('PFL Data'!F$3:F$5001,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(B2, 'PFL Data'!C$3:C$5001)),0),0)))="",(INDEX('PFL Data'!F$3:F$5001,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(B2, 'PFL Data'!C$3:C$5001)),0),0))="Unknown")))),1,0)
PFL data sheet is a range of cells with data separated by commas.
Let me know if i can provide anymore information.
Thanks.
Bookmarks