combined vlookup and hlookup in VBA

[Jessbiotech]

I have a giant spreadsheet that has 2 worksheets.
in the cells i have the following equation:

=IF((VLOOKUP($C14,'Avail Parameters'!$C$5:$M$296,(HLOOKUP(F$4, 'Avail Parameters'!$G$394:$M$395,2,FALSE)),FALSE))="Yes", "EnterData", " ")

I want to change this so that instead of saying "EnterData" I want it to turn the cell green. If the statement is false, i want the cell to be gray.

Any idea on how to do this???

 

[jeffreybrown]

Hello & Welcome to the Board,

You might want to check out Conditional Formatting

Let say this formula is in A1

Select conditinal formatting and enter two conditions

1. =A1="EnterData"

2. =A1=0

Pick a fill and if you don't want the words to show then make the font color the same as the fill

 

[Jessbiotech]

Thanks Jeff.

But I want to be able to have this conditional formating without putting anyting in the cells. I want the color to be the only indicator to put info in that cell.
I have a table in 1 worksheet that has row and column headers and where they meet it says Yes or no (no = blank). I want for a given column/row location on a different spreadsheet to turn green if this same row/header match has a yes in the other worksheet. This enables multiple columns on worksheet 2 to be essentially referencing a single column on worksheet 1. I couldn't figure out to put if statements in the conditional formating option.
thoughts?

 

[jeffreybrown]

Ok, let me see if I understand.

Sheet1!A1 = "Yes so you want Sheet2!A1 to highlight...

AFAIK, you can't reference and cell from one worksheet to another by typing the reference in conditional formatting; however, I did find this.

http://excel.tips.net/Pages/T003433_Applying_Conditional_Formatting_to_Multiple_Worksheets.html

Actually by naming ranges you can reference cells through conditional formatting, but for what you want to do this might be way to big.

The only other alternative is most likely VBA and at this time I don't really have something to make that work. I will try and work something out, but I can't promise anything.

Maybe somebody who has a lot more knowledge than I can lend a template for this type of VBA.