Multiple If condition VBA, conditions are skipped

[bmbalamurali]

Hi All,

Thanks everyone for solving my issue every time.

Here is my next one, i have created below multiple if condition macro
usually i'll have 1000's of data and here in my attachment i have given less data as sample.

1. If product column is G4 then highlight the row with red color and bold and no further action required in that row, go to next row

2. If product is not G4, then check bank column if it is DER or COM or OIL and also in A/c balance column value is less than 10000, if both are true then highlight the row with light yellow color, and go to next row

3. if product is not G4 and bank is not DER or COM or OIL, then check level column is ETT3 and column A/c balance in less than 500 and if both are true highlight that row with light yellow color and go to next row

4. If Product column is not G4 next
If Bank is not DER or COM or OIL ,, or if it is DER or COM or OIL and A/c balance is not less than 10000 next
If level column is not ETT3 or if its is ETT3 and A/c balance is not less than 500 next

Then

directly Check A/c balance column, whether it's value is less than 5% leverage of value in Min balance. ( that is only 5 % of leverage of Min balance is allowed to maintain in A/c balance)
If A/c balance value is less than (Min Balance - (0.05*Min Balance)), then highlight the row with light yellow color and go to next row.

Have attached my excel, If am not clear kindly come back.

Here in my code some conditions are skipped or clash occurs with other conditions and not working in order.
Kindly correct me in my macro code. Will keep learning. Thanks.

[Bernie Deitrick]

Try it this way:

Please Login or Register  to view this content.

[bmbalamurali]

Hi Trick,

This way of if and loop is nice and simple to understand too..

But its not giving the correct output.

Lookig for G4 is fine

Looking for DER or OIL or COM and also < 10000

in this macro highlights all DER, OIL and COM , it doesn't considering its value <10000 or not. if its > 10000 its highlighting

Looking for ETT3 and also < 500

in this macro is looking for ETT3 and < 500 and highlights it, but in case of value is > 500 also it highlights because it considering the next step also... that is value less than leverage of 5% (0.05). macro should see for ETT3 and <500 if both are true it highlight or it goes to next row.

These above three conditions are unique, if any of the three condition is true it highlight and macro should go to next row
if false then only it should check for leverage 5% condition.


Last condition( 5% leverage) should be checked in the same row only if all first three uique conditions are false


Myself confused in explaining above, if am not clear please come back Thanks.

[Bernie Deitrick]

No, I'm not confused, but you are, because you used the same format condition for the last three conditions. Try it with different formatting so you can see what is what...this also writes out the condition that caused the formatting to occur.. One other possibility is that the code you posted did not work with the file you posted - I had to change one of the offsets from 6 columns to 5 to use the A/c balance values....

Please Login or Register  to view this content.

[bmbalamurali]

Hi Trick,

Thanks for your detailed help....

I tried with each every conditions seperately and found the below condition not giving the output correctly... i wonder why it is doing so...

Please Login or Register  to view this content.

In this if its COM , DER its not seeing the next condition < 10000
only in last condition OIL its working...

Is that do we need to give this < 10000 thing in each condition of DER COM and OIL. Dont know why...

Please help Thanks... others are fine... i will validate again for my comfort...

[shg]

Precedence of operators:

Please Login or Register  to view this content.

[Bernie Deitrick]

Ooops - forgot parens...

Please Login or Register  to view this content.