Solved

Question to revise VBS script excel conditional formatting

Posted on 2009-05-05
10
272 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

740 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