Function in Conditional Formatting Not Working

try this (untested, as I dont have your data)...
=ISNUMBER(MATCH(INDIRECT("S"&ROW()+4),INDIRECT(ConvertToLetter(MATCH(INDIRECT("R"&ROW()+4),DropDown!1:1,0))),0))

That will return either a TRUE or FALSE value by itself, and thats what CF needs. With complex formulas like that for CF, I often create them IN the worksheet, and play with them until i get the required T or F answer 0 its much easier to edit in the W/S than in the CF window

Thank you for your reply!

I have tried your code and the CF still isn't working properly despite me getting the correct T and F

The function I have should be correct. I tested it in different scenarios and I obtained the expected results.

In terms of testing, I'm observing the output of the function in the worksheet. So when it results in true in the worksheet, it should also be resulting in true in the CF since they have the same formula.

If you enter that formula, copied down, to where you want the CF applied, does it giove the required T/F?
Have you got the range correct?
Is the CF being applied starting at the correct row?
The formula seems pretty complex, why the need for the indirects?

2. Range? I'm using the "Use a formula to determine which cells to format" Rule type so I don't have a range.

There has to be a range, but guessing isnt going to solve this 1, I will wait until you can provide a sample file

I apologize for taking so long with the sample file!
I had successfully replicated the problem using the same environment of the original file.

So for the new file, I want to check if the item in the cell 'B2' is in the category for cell 'A2'.
So if the item is 'Apple' and category is 'Fruit', then it should return True.
If the item is 'Apple' and the category is 'Vegetable', then it should return False.

I'm using cell 'D2' as a form of feedback to ensure that I am getting the correct True/False.
The Conditional Formatting takes place in cell 'B2'; it is using the same formula that cell 'D2' is using.
In other words, if cell 'D2' is true, then the CF in cell 'B2' should be true, which would result it in filling the cell with the color light-blue.

Thank you for your help so far!

You must upload your files directly to the forum. Don't make members take side excursions. Besides, it's easier.
To Attach a Workbook:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit

Haven't figured out the why, but apparently your formula results in TRUE in the W/S but not in CF. I replaced the CF formula with =D2 and it correctly highlighted B2.

BTW, exactly what are you trying to accomplish with the formula... the contents A2 and B2 match, on DropDown sheet, the header Fruit in B2 and one of the members below it? It appears you've already accomplished that with data validation. However, I'm aware that data validation falls short once the value is selected, because either A or B entry can be deleted and there will be no error message. I suggest applying CF to highlight (red?) A or B entries if they are blank.

Try this simpler approach (although, not knowing what your real data looks like, you will have to test)
I added a helper column on sheet2 after B (Fruit) and used this, copied down, to give me a fruit/type combo
=B$1&B2
I gave this a named range Fruit1
Then I did same for Vegetable (Vegetable1)

Then I used this for my CF...
Highlight B2:B5
click CF - new rule - use formula
=ISERROR(VLOOKUP(A2&B2,INDIRECT(A2&1),1,0)) format fill RED

I am using 2007, it does not have ConvertToLetter, nor can it reference another sheet directly

Okay, if you Highlight B2-B#, and apply this formula :
=MATCH(B2,Vegetable,0)
then select format, fill, red
it will highlight all the vegetables in red, change the 'Vegetable' to 'Fruit' and you get that highlighted;

Not exactly sure what you expect from the sample...there is no explanation of why the cell(s) should be hi-lighted or not so ...

@ Dred, I think they want to highlite the 2nd D/D if the 1st was changed and the 2nd doesnt match up now?

Maybe this then :

Formula:

=ISERROR(MATCH(B2,INDIRECT(A2),0))

Drag down

I moved "Lettuce" int the 'Fruit' list and changed the range definition for fruit to include it (probable unnecessary, but better safe than sorry)
This highlights any value in column B that does not exist in the named range from column a

Hope this helps

AHH, okay, think I'm seeing the picture now...

well, thats what makes sense to me, anyway - I have been know to be wrong before lol

Well...I did miss the expansion to include lettuce, thanks for the heads up...however, MY machine does NOT return an error for the Indirect(a2), it returns the 'named range' range values, (check column E to see the worksheet results of the formula), so not sure what is happening on your machine..

LOL , it happens

@ jhren, TY for the answer "yes"
@quack618
You are quite welcome