Cell Color as Variable

[CBrechin]

I am playing with the idea of checking a cell's value against a reference table. In my original document it has to be a table because the number of rows is always changing. I have played with two simple FOR loops, but it seems that I have not been able to declare the color of the reference cell as a variable. I have been able to check the value of the cell by using:

If inputCell.Value = Cells(i, 1) Then inputCell.Interior.Color = RGB(255, 0, 0) and it changes the according cells.

I need the final program to cycle through the list in the reference table ("Table1") and pull the Interior.Color of each cell IF inputCell.Value = Cells(i, 1) ELSE move on to the next row.

My final project is a bit more complicated so I decided to boil it down into a simple one worksheet document. Does anyone know a simple (or simpler) solution to validate EACH cell in a table against EACH cell in another table?

Thanks in advance

[humdingaling]

having trouble understanding what is your expected output is
did you want table 2 to be colored in with the reference of table1?

[CBrechin]

Yes. I want to see if Cell(i, j).Value from Table2 matches Cell(i, 1) from Table1. If it finds a matching value, I want Table1 cells' color to be copied over to the destination cell in Table2.

[Winon]

Hello CBrechin,

You could apply Conditional Formatting as shown in the attached sample Workbook. It is almost always better to use built in Excel features, rather than reverting to VBA.

Regards.

[CBrechin]

I realize this. But my project is much larger than this sheet. I have conditional formatting already for the one worksheet. But because you can not change conditional formatting to match a dynamic range of values, I have to use VBA for a listening Sub to push the new color over from one table to the next.

I have to find a way to do it with VBA. Thanks for the suggestion, I had explored that option extensively before coming to the conclusion that VBA was the most effective.

[humdingaling]

ok the way you had the syntax was a bit ambigious
try this solution

Please Login or Register  to view this content.

[CBrechin]

Wow that works fantastically! Sorry for my ambiguity, I am still learning how to better phrase my questions.

I like what you did with the tbl variable. Smart idea.

Why do you use End If at the end of the Sub?

Thanks again.

[humdingaling]

hhmmm

You need to end if, if your IF statement runs more than one line

Please Login or Register  to view this content.

^ no end if required

Please Login or Register  to view this content.

end if required to close the IF statement

[Winon]

@ humdingaling,

Nice work.!

Little touch up?

Regards

[humdingaling]

nice touch

Even went to the trouble of coloring in the font color

[Winon]

Hello CBrechin,

You are welcome, thank you for the Rep+. Much appreciated!

Regards.