Conditional Formatting VBA with formula to find string

Hi,
Please can I get some help....
I need to create a conditional format VBA in Excel 2007 (because I have many 
conditions to include) and I don't know how to do it...

The Action Required:
If Product A appears in any text string in range($C$95:$C$300) then colour 
that cell RED,
if Product B appears anywhere in a text string within range($C$95:$C$300) 
then colour that cell BLUE,
if Product B appears anywhere in a text string within range($C$95:$C$300) 
then colour that cell GREEN,
and so on through 41 products...

Data
-  I have a list of about 41 Product names in range Z74:Z114.

-  Data cells are in range($C$95:$C$300) which is named "Prod_name" 
This list of data will quote the product name somewhere in the text string.


e.g.
Data
Cell C105 = "Special for Product One"
Cell C106 = "Product Six last offer"
Cell C107 = "Coming soon Product One plus more"
Cell C110 = "Today Product Three are in"

Product LIst
    Product One
    Product Two
    Product Three
    Product Four
    Product Five
    Product Six
etc.

I need cell C105 to colour fill RED
I need cell C106 to colour fill BLUE
I need cell C107 to colour fill RED (same as C105 because they're both 
Product One)
I need cell C110 to colour fill GREEN
etc through the data range.
If no match is found, the data cell does not get filled.

I tried to use the Conditional Formatting "Case" functions but I couldn't 
get it to accept a Range (to look at the product names), or a formula e.g. 
SEARCH or MATCH.

Any help would be greatly appreciated.
Cheers
BeSmart
0
Utf
1/28/2010 7:14:01 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
904 Views

Similar Articles

[PageSpeed] 30

Not sure I quite follow all the details, but to simplify I put your 4 data 
cells in A1:A4
I selected A1:A4
then added this conditional format
IsFormula
=ISERROR(FIND("Product One", A1))=FALSE
and added a format
When done CFs in A1 and A3 were triggered.
I could have gone on to add further CFs the same way

If "Product One" is in say cell F1 on the same sheet, could change "Product 
One" to $F$1 in the formula

Regards,
Peter T

"BeSmart" <BeSmart@discussions.microsoft.com> wrote in message 
news:B58875A9-5292-4FE3-9D8B-E978783461E9@microsoft.com...
> Hi,
> Please can I get some help....
> I need to create a conditional format VBA in Excel 2007 (because I have 
> many
> conditions to include) and I don't know how to do it...
>
> The Action Required:
> If Product A appears in any text string in range($C$95:$C$300) then colour
> that cell RED,
> if Product B appears anywhere in a text string within range($C$95:$C$300)
> then colour that cell BLUE,
> if Product B appears anywhere in a text string within range($C$95:$C$300)
> then colour that cell GREEN,
> and so on through 41 products...
>
> Data
> -  I have a list of about 41 Product names in range Z74:Z114.
>
> -  Data cells are in range($C$95:$C$300) which is named "Prod_name"
> This list of data will quote the product name somewhere in the text 
> string.
>
>
> e.g.
> Data
> Cell C105 = "Special for Product One"
> Cell C106 = "Product Six last offer"
> Cell C107 = "Coming soon Product One plus more"
> Cell C110 = "Today Product Three are in"
>
> Product LIst
>    Product One
>    Product Two
>    Product Three
>    Product Four
>    Product Five
>    Product Six
> etc.
>
> I need cell C105 to colour fill RED
> I need cell C106 to colour fill BLUE
> I need cell C107 to colour fill RED (same as C105 because they're both
> Product One)
> I need cell C110 to colour fill GREEN
> etc through the data range.
> If no match is found, the data cell does not get filled.
>
> I tried to use the Conditional Formatting "Case" functions but I couldn't
> get it to accept a Range (to look at the product names), or a formula e.g.
> SEARCH or MATCH.
>
> Any help would be greatly appreciated.
> Cheers
> BeSmart 


0
Peter
1/28/2010 9:57:05 AM
Try some code similar to this:

Option Explicit
Sub Liminal()
Dim Cell As Range

For Each Cell In Range("$C$95:$C$300")
    If Cell.Value Like "*Product A*" Then
        Cell.Interior.Color = vbRed
    ElseIf Cell.Value Like "*Product B*" Then
        Cell.Interior.Color = vbBlue
    ElseIf Cell.Value Like "*Product C*" Then
        Cell.Interior.Color = vbGreen
    End If
Next Cell

End Sub

Modify the product names to suit and add as many "ElseIf" sections as 
needed, being sure to keep the * both before and after the string (and within 
the double quotes) that corresponds to the product name. I  performed a small 
test and it worked fine.

HTH
Bill
"BeSmart" wrote:

> Hi,
> Please can I get some help....
> I need to create a conditional format VBA in Excel 2007 (because I have many 
> conditions to include) and I don't know how to do it...
> 
> The Action Required:
> If Product A appears in any text string in range($C$95:$C$300) then colour 
> that cell RED,
> if Product B appears anywhere in a text string within range($C$95:$C$300) 
> then colour that cell BLUE,
> if Product B appears anywhere in a text string within range($C$95:$C$300) 
> then colour that cell GREEN,
> and so on through 41 products...
> 
> Data
> -  I have a list of about 41 Product names in range Z74:Z114.
> 
> -  Data cells are in range($C$95:$C$300) which is named "Prod_name" 
> This list of data will quote the product name somewhere in the text string.
> 
> 
> e.g.
> Data
> Cell C105 = "Special for Product One"
> Cell C106 = "Product Six last offer"
> Cell C107 = "Coming soon Product One plus more"
> Cell C110 = "Today Product Three are in"
> 
> Product LIst
>     Product One
>     Product Two
>     Product Three
>     Product Four
>     Product Five
>     Product Six
> etc.
> 
> I need cell C105 to colour fill RED
> I need cell C106 to colour fill BLUE
> I need cell C107 to colour fill RED (same as C105 because they're both 
> Product One)
> I need cell C110 to colour fill GREEN
> etc through the data range.
> If no match is found, the data cell does not get filled.
> 
> I tried to use the Conditional Formatting "Case" functions but I couldn't 
> get it to accept a Range (to look at the product names), or a formula e.g. 
> SEARCH or MATCH.
> 
> Any help would be greatly appreciated.
> Cheers
> BeSmart
0
Utf
1/28/2010 2:34:08 PM
Thanks Fisch - that works great, however we will have new products that will 
need to be added and it would be better to do this via a list of product 
names rather than constantly having to update the code.

Is there a way for the code to lookup each "product name" from a list (range 
Z74:Z114 on the current worksheet) and searched for it in strings?

e.g.
     ElseIf Cell.Value Like "*Product B*" Then

 "*Product B*" needs to look at the next name in the list (Z74:Z114) & 
search for that product name in the strings.

That way the users can add/change the product names as required without the 
code having to be updated?

-- 
Thank for your help
BeSmart


"fisch4bill" wrote:

> Try some code similar to this:
> 
> Option Explicit
> Sub Liminal()
> Dim Cell As Range
> 
> For Each Cell In Range("$C$95:$C$300")
>     If Cell.Value Like "*Product A*" Then
>         Cell.Interior.Color = vbRed
>     ElseIf Cell.Value Like "*Product B*" Then
>         Cell.Interior.Color = vbBlue
>     ElseIf Cell.Value Like "*Product C*" Then
>         Cell.Interior.Color = vbGreen
>     End If
> Next Cell
> 
> End Sub
> 
> Modify the product names to suit and add as many "ElseIf" sections as 
> needed, being sure to keep the * both before and after the string (and within 
> the double quotes) that corresponds to the product name. I  performed a small 
> test and it worked fine.
> 
> HTH
> Bill
> "BeSmart" wrote:
> 
> > Hi,
> > Please can I get some help....
> > I need to create a conditional format VBA in Excel 2007 (because I have many 
> > conditions to include) and I don't know how to do it...
> > 
> > The Action Required:
> > If Product A appears in any text string in range($C$95:$C$300) then colour 
> > that cell RED,
> > if Product B appears anywhere in a text string within range($C$95:$C$300) 
> > then colour that cell BLUE,
> > if Product B appears anywhere in a text string within range($C$95:$C$300) 
> > then colour that cell GREEN,
> > and so on through 41 products...
> > 
> > Data
> > -  I have a list of about 41 Product names in range Z74:Z114.
> > 
> > -  Data cells are in range($C$95:$C$300) which is named "Prod_name" 
> > This list of data will quote the product name somewhere in the text string.
> > 
> > 
> > e.g.
> > Data
> > Cell C105 = "Special for Product One"
> > Cell C106 = "Product Six last offer"
> > Cell C107 = "Coming soon Product One plus more"
> > Cell C110 = "Today Product Three are in"
> > 
> > Product LIst
> >     Product One
> >     Product Two
> >     Product Three
> >     Product Four
> >     Product Five
> >     Product Six
> > etc.
> > 
> > I need cell C105 to colour fill RED
> > I need cell C106 to colour fill BLUE
> > I need cell C107 to colour fill RED (same as C105 because they're both 
> > Product One)
> > I need cell C110 to colour fill GREEN
> > etc through the data range.
> > If no match is found, the data cell does not get filled.
> > 
> > I tried to use the Conditional Formatting "Case" functions but I couldn't 
> > get it to accept a Range (to look at the product names), or a formula e.g. 
> > SEARCH or MATCH.
> > 
> > Any help would be greatly appreciated.
> > Cheers
> > BeSmart
0
Utf
2/1/2010 3:33:01 AM
Reply:

Similar Artilces:

VBA to check for latest version of front end?
I have a split database where the users have a local copy of the frontend. I would like to use VBA to check whether the users have the mostrecent version. I have set up the following tables:tVersion-Back-End (link to table in the BackEnd version)tVersion-FrontEnd (local table in the FrontEnd version)Each has a field Version where I am planning to put in the versionnumbers.Now, I am just starting to use VBA in my Access projects. Can someonepoint me to some coding to accomplish my task above? Can someone alsopoint me to a good resource (book, website, course) that would take mefrom a beginn...

VBA training suggestions?
I'm looking for tips on software out there on learning VBA for Excel. I know how to create macros and have a good idea of what a script might look like for the macro I created. I just want to learn how to write them on my own for my personal use. Any suggestions? Thank you kindly for your input! I do not know of any software for learning VBA, but you can't go wrong looking at all the Excel VBA web sites as well as purchasing a copy of John Walkenbach's "Excel 2003 Power Programming with VBA" http://j-walk.com/ss/. Recording macros and then editing them is a pow...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

Non-VBA way of making custom menus.
Sorry for the new thread but even on Google, the thread isn't showing up. Well, it was SOOO easy, as I knew it would be. The webpage I quoted in my message this morning didn't mention the "New Menu" at all! So, here is the non-VBA way to create a custom menu: - TOOLS > CUSTOMIZE - under the categories available choose NEW MENU and then drag the NEW MENU option under the Commands window up to the menu bar (like D'UH!! <lol> Wish all those hours spent searching yielded webpages that gave this! <g>) - THEN one can go back to the MACROS category a few line...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

Help with simple(?) VBA function
I'm trying to selectively BOLD cells by the use of a User-Defined function. No joy. The VBA Help topics suggest something like this: Function Bold() Worksheets("Sheet1").Range("A1:A5").Font.Bold = True End Function When I try to use it the referenced cells are not changed and the function returns "0". Can anyone point this VBA neophyte in the right direction? Thanks, -Dick- Hi Dick, A function can only return a value. Macros and Functions (Macros as Opposed to Functions) http://www.cpearson.com/excel/differen.htm If all...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

formula auditing/macro
Can anyone give me the sytax to goto - special - precedents so I can create a macro so I can assign to a hotkey and dont have to go through 4 steps ? Thanks, Yosef With A1=1 and D2=2*A1, and D1 as active cell: I recorded a macro for these steps: Edit|GoTo->Special->Precedence And the macro contained just one line: Selection.DirectPrecedents.Select best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:DA544BDE-3717-4953-A5E3-06191BC28373@microsoft.com... > Can anyone...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

find data and autopaste when found
Hi, Can someone help me how to do this : For checken the backorders of our customers we can extract a list fro our SAP system. this list is always different and shows us ever product per customer in Back order. ex. Customer A has product 1 en in backorder. This gives 2 lines in the xls file. can excel put th name of the customer on a form and it's backorders automatically. Ca it create for each customer showing in the list a new form? thanks koenraa -- Message posted from http://www.ExcelForum.com ...

find instance of IE for server socket in activex component
Hi I have an activex component that has a server socket (using casyncsocket class) ..The problem is that when I have multiple instances of the IE open only the first instance (created first) recieves the messages the rest do not recieve the message. Is there a way to solve this problem .(i want the server send the reply to that instance of ie that sent the message) .Do I need to pass something like a pointer of hte javascript object .If so how Thanks When you say "server socket", that suggests a socket that does a Listen followed by an Accept. Otherwise it is a client socke...

Formatting in CSV
Hi I create a Comma Separated Value file (csv) from my VB application, then open that file with Excel. Excel recognises the commas, and splits the records into separate fields as expected. Can I set properties for rows or columns or cells ? Like bolding, or font size, etc. Thanks Robert -- RobertLees ------------------------------------------------------------------------ RobertLees's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26580 View this thread: http://www.excelforum.com/showthread.php?threadid=486488 Yes. Even though the .csv file contains no fo...

Format List Box!
Can I format a list box which has a query,so if one of the fields called [Spelling] had the word "Spelling" it will change the colour of the font for that row Thanks for any help....Bob It's not possible with the native Access ListBox control. Instead, setup a Subform control to resemble a ListBox and use ConditionalFormatting to achieve the desired look. I think Arvin has a sample on his site here: http://www.datastrat.com/Download2.html -- HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Please respond only to the newsgroups so everyone can ben...

If statment with two conditions
I have an IF statement with two condtions as noted below. I want it to work so that if column G has a y in it, it will be hidden, and also if column G has an N in it and column O has a 0 then it will be hidden. The second part doesnt work. Any thoughts? Sub Hide_new() Dim cell As Range Dim rngisect As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set rngisect = Application.Intersect(ActiveSheet.UsedRange, Range("G19: G4061")) For Each cell In rngisect If cell.Value = "Y" Then cell.EntireRow.Hidden ...

How can I sum only amounts that are in BOLD format within a column
Hi! I need to get a total from a worksheet that has hundreds of amounts in it. However, I only need the total of the amounts that were marked with have BOLD font. Please help me I don't have much experience with EXCEL. Thank you very much! W a n d a try this for column D Sub sumbold() x = Cells(Rows.Count, "d").End(xlUp).Row For Each c In Range(Cells(2, 4), Cells(x, 4)) If c.Font.Bold Then mysum = mysum + c Next MsgBox mysum End Sub -- Don Guillett SalesAid Software donaldb@281.com "Wanda" <Wanda@discussions.microsoft.com> wrote in message news:89A268A6-...