Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

**sbv1986** · 05-04-2019, 10:53 AM

Hi all

I have work book with 02 sheet: data and result.
Sheet(data) have file name of folder in column(A) form A2 to last row (about over 200 rows)
Sheets(result) have 6 character each cell from B2 to lastrow, have 8 characters each cell from C1 to last column

Now I want to have macro to do that:
For each cell in sheet(result).range(C2,cells(lasrow, lastcolumn))
if value meet in sheet(data).range(A2:A)
cell.value = "YES"
else
cell.value = "NO"

Please me to do that, I attachment file belove for my idea. Thanks./.

**sintek** · 05-04-2019, 12:03 PM

Give this a go...

Please Login or Register  to view this content.

**Richard Buttrey** · 05-04-2019, 12:09 PM

Hi,

One way as an array formula entered with Ctrl--Shift--Enter in C2 Copied across and down

Formula:

Please Login or Register  to view this content.

Use a Conditiona format to paint the cell red

As a macro just get the macro to enter the formula for you.

**sbv1986** · 05-04-2019, 12:23 PM

Thanks @sintek code work but the font when mark "NO" still black color, can you have some code to "NO" is red color?

**sintek** · 05-04-2019, 01:00 PM

No way I know of to pass color via Array...Only can use conditional format per cell or...

Please Login or Register  to view this content.

**Richard Buttrey** · 05-04-2019, 02:37 PM

Originally Posted by sbv1986

Thanks @sintek code work but the font when mark "NO" still black color, can you have some code to "NO" is red color?

Never use code when there is a perfectly workable Excel solution. In this case conditional formatting.
There's a big time overhead each occasion you access a cell from VBA.

**sbv1986** · 05-04-2019, 10:29 PM

Thanks all, because range dymanic so I want to do thí by VBA code.

**sintek** · 05-05-2019, 04:25 AM

...............................................
Thanks.gif

**sbv1986** · 05-05-2019, 04:48 AM

@sintek

When I change value row to column code yellow line "Temp(x, xx) = "NO"" with info: subscrips out of range.

How can I fixed it?

**sintek** · 05-05-2019, 04:52 AM

Code was written for your other sample file setup...This is a new setup...
Change...

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

**sbv1986** · 05-05-2019, 05:03 AM

Thanks again, you're kind.

I added reputation yesterday so I can't add again today ^^!

**sintek** · 05-05-2019, 05:05 AM

Anytime...Good luck...

**Richard Buttrey** · 05-05-2019, 05:23 AM

Originally Posted by sbv1986

Thanks all, because range dymanic so I want to do thí by VBA code.

Juts because a range is dynamic doesn't mean you necessarily need VBA. Just create an Excel dynamic range name and use that rather than a specific range.

e.g. with a name 'List' defined as

Formula:

Please Login or Register  to view this content.

the array formula in C2 then becomes

Formula:

Please Login or Register  to view this content.

**sbv1986** · 05-05-2019, 05:52 AM

Originally Posted by Richard Buttrey

Juts because a range is dynamic doesn't mean you necessarily need VBA. Just create an Excel dynamic range name and use that rather than a specific range.

e.g. with a name 'List' defined as

Formula:

Please Login or Register  to view this content.

the array formula in C2 then becomes

Formula:

Please Login or Register  to view this content.

Thanks Richard, I'll try your solution.

Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

LinkBack

Thread Tools

Rate This Thread

Display

Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Re: Help to check data with miltiple conditions then mark "YES" if meet else mark "NO"

Thread Information

Users Browsing this Thread

Similar Threads

a formula that pulls data from an array to mark a single cell "X"

Automate Series Number With Adding Mark (.) or mark "-"

[SOLVED] Counting data only contains text (ignore mark "-" & "")

Comparing data between 2 worksheets, mark them as "yes' or "no" based on several condition

Inserting a "check Mark"

Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next

[SOLVED] how can I make an excel cell "mark" or "unmark" when clicked on?

Bookmarks

Bookmarks

Posting Permissions