Re: Using VB to create "conditional formatting"

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Jim Thomlinson (JimThomlinson_at_discussions.microsoft.com)
Date: 03/07/05 

Date: Mon, 7 Mar 2005 14:27:03 -0800

You can not just record what you are asking for. It requires programming. We
can give you some pointers on how to writ the code but we can't just write it
for you. Your request is a bit to large for that. Here si a general outline
of what you might want. If you understand this then with some tinkering
around you might be able to accompliosh what you want:

public sub FormatCells()
dim rngCurrentCell as range

for each rngcurrentcell in usedrange
  if instr(CurrentCell.value, "lunch") > 0 then
     rngCurrentCell.interior... 'apply your format
  endif

  if instr(CurrentCell.value, "off") = 0 then
     rngCurrentCell.interior... 'apply your format
  endif

next rngCurrentCell

end sub

Try playing with this and see if you can get it to go... If you can and you
still have some specific questions then let us know...

HTH

"Fleone" wrote:

> Perhaps I am not using the correct terminology. I am making the assumption
> that a Macro is not actually Visual Basic programming in Excel, but a
> "recording", if you will, of repetitive tasks. If my use of the term VB is
> incorrect, I apologize for the confusion.
> I would merely like to have cells formatted based on their contents and
> Conditional Formatting doesn't fulfill my needs in this instance due to the
> limitation in the number of formatting instances that are allowed.
> When attempting to use CF+ it would not apply my formatting request over the
> range of cells that I specified. They were all concurrent cells (Range
> B4:U14) and the CF+ add-in would apply the same formatting to the entire
> range. I believe what was occuring is that the CF+ add-in was making a
> reference to a single cell in the range, not the entire range.
>
>
> "Bob Phillips" wrote:
>
> > So you want more than 3 conditions, you can't use CFPlus, you don't want to
> > use a macro (even though the subject says using VB ...).
> >
> > To quote the proverb, you want your cake and eat it too. Can't be done.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Fleone" <Fleone@discussions.microsoft.com> wrote in message
> > news:51E2DCCF-7342-48CA-823F-14D330BF8C0E@microsoft.com...
> > > I can't really use Conditional Formatting because I will be exceeding the
> > 3
> > > formats allowed by Excel.
> > > I tried the Add-in that was posted here by Frank Kabel some time ago, and
> > it
> > > is not fitting my needs either.
> > > One thing I would like to avoid is having to have another user of the
> > > workbook have to install an addin, or have access to a Macro in order to
> > > view, or alter the formatting or contents of the workbook itself.
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Why use VB. You can use regular conditional formatting.
> > > > Change from Cell Value is to Formual is and use something like
> > > >
> > > > =FIND("lunch", A1)>0
> > > >
> > > > If it finds the string lunch anywhre in the cell it returns true and
> > formats
> > > > the cell. Off can be done in much the same way...
> > > >
> > > > =FIND("off", A1)=0
> > > >
> > > > HTH
> > > >
> > > > "Fleone" wrote:
> > > >
> > > > > I would like to have cells in a workbook formatted depending on their
> > > > > contents, not their formulas.
> > > > > For example: If a cell contains the word "Lunch" I want the cell to be
> > > > > formatted in Yellow, all through the workbook.
> > > > > Transversely, if a cell does not contain the word "Off", I would like
> > it to
> > > > > be Blue, all through the workbook.
> > > > > I have little practical Visual Basic experience and most of what I
> > have
> > > > > already picked up has been from reverse engineering other scripts.
> > > > > Would appreciate any assistance.
> > > > > Thanks.
> >
> >
> >

Quantcast