• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Highlight rows based on condition in VBA

C

coolkiran

Member
Hi all

IS that possible to highlight rows based on the following.

My column contains combination of "Name"&"Amount.
Eg:
Kiran1000
Rajesh2000

but in some cases i have
Kiran-1000

So i wanted to highlight Both rows Kiran1000 and Kiran-1000.

I have unique names, shown is an example.

Any code we can try.
 
Narayan

That file is fine, one thing i want to check, if only negative value is there, then only it has to check the positive number, then highlight the row. Is that possbile. IF its only positive duplicates, no need to highlight.
 
Hi ,

Do you mean that if there are two entries against Kiran1000 , neither of them will be highlighted ?

Also , if there are two entries Kiran1000 and Kiran-1000 , are both to be highlighted or is only Kiran-1000 to be highlighted ?

Narayan
 
NARAYANK991 said:
Hi ,

Do you mean that if there are two entries against Kiran1000 , neither of them will be highlighted ?

Also , if there are two entries Kiran1000 and Kiran-1000 , are both to be highlighted or is only Kiran-1000 to be highlighted ?

Narayan
Click to expand...
Yes, if Kiran1000 and Kiran-1000 found, then i need to highlight and also it would be multiple time, i mean Kiran1000 (3 times) and Kiran-1000 (2 times), that case i need to highlight all 5 rows.

Basically if any negative found, i need to check all positive of that and highlight.
If only positive is there, no need to highlight it.
 
Hi ,

Anything that can be done using formulae can certainly be done using VBA.

Conditional Formatting is implemented using Excel formulae , and can also be implemented using VBA.

Narayan
 
NARAYANK991 said:
Hi ,

Anything that can be done using formulae can certainly be done using VBA.

Conditional Formatting is implemented using Excel formulae , and can also be implemented using VBA.

Narayan
Click to expand...

I tried to add the formulas to two cells.
Code: 
=IF(MATCH(REPLACE($R1, 1/MIN(IF(1,IFERROR(1/FIND(Digits, $R1),65536))), 0, "-"),$r$1:$r$65536,0),1,0)
Code: 
=IF(ISNUMBER(FIND("-", $R1)), IF(SUMPRODUCT((SUBSTITUTE($R$1:$R$65536,"-","")=SUBSTITUTE($R1,"-","")) * ($R$1:$R$65536 <> "")) > 1, 1, 0))

My data is like below :
2177GL SETTTING14236067.26Y1
2177GL SETTTING14236067.26Y1
2177GL SETTTING14236067.26Y1
2177GL SETTTING-14236067.26Y1
2177GL SETTTING14236068.26Y1
2177GL SETTTING14236068.26Y1


All Negative values showing as "1" but its matching positive values not getting highlighted. so i was bit confused.
 
Hi ,

The problem is that the formula was written based on the data provided.

Your initial data was like this :

Kiran1000

where the first character is a letter , not a digit.

The data that you have now is like this :

2177GL SETTTING14236067.26Y1

where the first character is a digit.

If you eliminate the left-most 4 characters or if the number of digits on the left will be variable , the first group of characters (by looking for the space character) , then the following formula will work :

=IF(MATCH(REPLACE($R1, 1/MIN(IF(1,IFERROR(1/FIND(Digits, MID($R1,FIND(" ", $R1)+1, 255)),65536))), 0,"-"),$R$1:$R$65536,0),1,0)

where the revised part is highlighted.

Narayan
 
NARAYANK991 said:
Hi ,

The problem is that the formula was written based on the data provided.

Your initial data was like this :

Kiran1000

where the first character is a letter , not a digit.

The data that you have now is like this :

2177GL SETTTING14236067.26Y1

where the first character is a digit.

If you eliminate the left-most 4 characters or if the number of digits on the left will be variable , the first group of characters (by looking for the space character) , then the following formula will work :

=IF(MATCH(REPLACE($R1, 1/MIN(IF(1,IFERROR(1/FIND(Digits, MID($R1,FIND(" ", $R1)+1, 255)),65536))), 0,"-"),$R$1:$R$65536,0),1,0)

where the revised part is highlighted.

Narayan
Click to expand...

I am extremely sorry for that, because i was not aware that . In fact most of the data starts from digit and some start from Characters.

This is the concatenation of 4 columns for that reason, i have data like this.

Following thing i want to achieve :
1. Find duplicates. (highlight in one color)
2. Find negative value and its opposite value and highlight in different color.

I have attached my original file.
 

Attachments

  • Chandoo (2).xlsx
    15.6 KB · Views: 2
Last edited:
NARAYANK991 said:
Hi ,

Is this acceptable ? Columns B and C are the helper columns.

Narayan
Click to expand...
Thanks Narayan, Attached file gives, all duplicates in color, thats fine, but for each negative it will not check positive value and highlight . I will try it in Macro and let you know here. Mean time any other option please let me know.
 
Hi ,

The helper column C is stripping cells which have data with the hyphen , of the hyphen ; what remains is then checked for duplicates.

Narayan
 
You must log in or register to reply here.
Back
Top