Solved

Question to revise VBS script excel conditional formatting

Posted on 2009-05-05
10
273 Views
Last Modified: 2013-11-25
Hi all,

Recently I was helped to generate this script pasted below.  What I would like to do is revise it, is it possible instead of where the column = "##" to state where NOT, simular to SQL where you can have a statement like:

WHERE NOT (myfield in('1234','6789','0987'))

The section I am focusing on in the code is this:

For Each cell In rngH
  If cell<>"" Then
    If Instr(cell,"##") Then

Thanks for your help in advance!
Call DoStuff
 
Sub DoStuff()
 
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("D:\NewMonthly\export\newplayers\newplayers.XLS")
Set xlWs = xlwb.Sheets("newplayers.XLS")   'amend as appropriate
 
with xlWs
  Set rngJ = xlApp.Intersect(.UsedRange,.Range("R:R"))
  Set rngH = xlApp.Intersect(.UsedRange,.Range("H:H"))
End With
 
For Each cell in rngJ
  If Isnumeric(cell) Then
    If cell>50000 Then 
       cell.EntireRow.Interior.ColorIndex = 6
       cell.EntireRow.Font.Bold = True
    Else
       cell.EntireRow.Interior.ColorIndex = -4142
       cell.EntireRow.Font.Bold = False
    End If
  Else
       cell.EntireRow.Interior.ColorIndex = -4142
       cell.EntireRow.Font.Bold = False   
  End If
Next
 
For Each cell In rngH
  If cell<>"" Then
    If Instr(cell,"##") Then
       cell.EntireRow.Interior.ColorIndex = 3
       cell.EntireRow.Font.Bold = True
'           cell.Interior.ColorIndex = 6
'      cell.Font.Bold = True
    End If
  End If
Next
 
xlWb.Save
xlWb.close
 
xlApp.Quit
Set xlApp = Nothing
 
End Sub

Open in new window

0
Comment
Question by:smyers051972
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
10 Comments
 
LVL 6

Expert Comment

by:zipa72
ID: 24305565
Instr function will return a value greater than zero if it founds the match. If not, the value will be zero.
You should write the function like this:

If InStr(cell, "##") <1 Then
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24305687
What I am looking for however is a list of IF NOT's which could include criteria of 4 or 5 different ones and more of a BEGINS WITH scenario. I think I forgot to mention that above sorry...

So the criteria is more like : WHERE NOT '123%', '432%' and so fourth.
0
 
LVL 6

Expert Comment

by:zipa72
ID: 24305720
Do you have fixed number of criteria? If so, you can do something like:
If Instr(cell, "##")<1 And Instr(cell, "...")<1 And ... Then
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:smyers051972
ID: 24305809
Actually I used "##" as just an example.  This will be text based, I am looking at source of business called "sourcecode", so if the source code is anything like:

src1%
src2%
src5%

Delete (for example).

Under that criteria, if src15 was present, it would be deleted but if the source code was src35 it would remain.

Hope this helps :)
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24320603
Assuming you want to delete :-
  • src1%
  • src2%
  • src5%
Then you can use the following set of code which will do what you want ..
If Instr(cell, "src1")>1 or Instr(cell, "src2")>1 or Instr(cell, "src5")>1 then
Now this will delete src15% since it has src1, similary stands for src25%, However it wont delete src35%
Saurabh...
 
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24326269
Ok I will test this out, what about IF NOT's ? would I be correct to assume this can be done?
0
 
LVL 6

Accepted Solution

by:
zipa72 earned 500 total points
ID: 24326556
smyers051972,

The function InStr(...) returns the value grater than zero if the string is found.
That means that you can look at the equivalence for:

InStr(...)>=1   => DOES CONTAIN

and

InStr(...)<1 => DOES NOT CONTAIN

The rest is up to your program logic, for example if you want to contain "aaa" or "bbb", you will put:

If InStr(cell, "aaa")>=1 Or InStr(cell, "bbb") Then

but if you want it NOT to contain neither "ccc" and "ddd", you will put:

If InStr(cell, "ccc")<1 And InStr(cell, "ddd")<1 Then

hth
0
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31578038
VERY helpful! Thank you for explaining it to me!
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question