Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
Fleone,
I wrote CF+ with Frank. If you want to mail me direct, perhaps we could continue the discussion offline, and hopefully solve your problem (one way or another). -- HTH RP (remove nothere from the email address if mailing direct) "Fleone" wrote in message ... 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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
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" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
Thank you Bob, I appreciate your offer. Jim provided some code that I will
play with as well. "Bob Phillips" wrote: Fleone, I wrote CF+ with Frank. If you want to mail me direct, perhaps we could continue the discussion offline, and hopefully solve your problem (one way or another). -- HTH RP (remove nothere from the email address if mailing direct) "Fleone" wrote in message ... 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" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
Thank you for the code Jim. I will mess around with it and see what I can
figure out. As always, I appreciate the groups assistance. I hope one day to be providing answers here....not just questions. Thanks again. "Jim Thomlinson" wrote: 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" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
Using VBA.
Add a standard code module and paste the following: Option Explicit Enum eColors Yellow = 10092543 Green = 13434828 Blue = 16777164 Grey = 12632256 Orange = 10079487 End Enum Sub MyConditionalFormatting(Target As Range) Dim cell As Range Dim clr As Long For Each cell In Target Select Case UCase(Trim(cell.Value)) Case "LUNCH": clr = eColors.Yellow Case "OFF": clr = eColors.Blue Case "HOLIDAY": clr = eColors.Grey Case "COURSE": clr = eColors.Orange Case Else: clr = eColors.Green End Select cell.Interior.Color = clr Next End Sub Sub test() MyConditionalFormatting Range("MyData") End Sub To test On a sheet, select a range, name it "MyData" type some values into some cells in th erange randomly, include: Holiday, Lunch, Off, Course run the Test procedure. This passes the range "MyData" to the procedure that then does the formatting. This example allows four colors, but you can see that its quite easy to adapt to use more colors. HTH Patrick Molloy Microaoft Excel MVP "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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
Ok, I just got back on the forum here, and saw your response Patrick, I will
try this and see what it does. In the meantime, I found this buried in another workbook that I have been working with and made some tweaks and it seems to be working. I have a question about it though. Private Sub Worksheet_change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("C4:U13")) Is Nothing _ Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Lunch": .Interior.ColorIndex = 6 Case "Off": .Interior.ColorIndex = 0 Case "Vacation": .Interior.ColorIndex = 34 Case "Call Off": .Interior.ColorIndex = 44 Case "Holiday": .Interior.ColorIndex = 43 Case "Meeting": .Interior.ColorIndex = 35 Case "Project": .Interior.ColorIndex = 36 Case "Training": .Interior.ColorIndex = 37 End Select End With CleanUp: Application.EnableEvents = True End Sub This is working GREAT for changing any of the cell colors in any worksheet that I add this code to, so as a method for defeating the conditional formatting limitation, it is doing exactly what I would expect. The next step in this would be to have the ability to affect a change to the font color in each of the cells according to their content. I have already tried making another entry in the worksheet identical to the one above, but changing Interior.ColorIndex to Font.ColorIndex. That doesn't work, I recieve an error on using an "ambigous" <sp? name. When I try to duplicate one of the lines, and making the same change, it doesn't work either. It has become a real head scratcher <G Any insight you can provide would be great! I will try the code that you have already provided. Thanks again! "Patrick Molloy" wrote: Using VBA. Add a standard code module and paste the following: Option Explicit Enum eColors Yellow = 10092543 Green = 13434828 Blue = 16777164 Grey = 12632256 Orange = 10079487 End Enum Sub MyConditionalFormatting(Target As Range) Dim cell As Range Dim clr As Long For Each cell In Target Select Case UCase(Trim(cell.Value)) Case "LUNCH": clr = eColors.Yellow Case "OFF": clr = eColors.Blue Case "HOLIDAY": clr = eColors.Grey Case "COURSE": clr = eColors.Orange Case Else: clr = eColors.Green End Select cell.Interior.Color = clr Next End Sub Sub test() MyConditionalFormatting Range("MyData") End Sub To test On a sheet, select a range, name it "MyData" type some values into some cells in th erange randomly, include: Holiday, Lunch, Off, Course run the Test procedure. This passes the range "MyData" to the procedure that then does the formatting. This example allows four colors, but you can see that its quite easy to adapt to use more colors. HTH Patrick Molloy Microaoft Excel MVP "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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("C4:U13")) Is Nothing _ Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Lunch": .Font.ColorIndex = 6 Case "Off": .Font.ColorIndex = 0 Case "Vacation": .Font.ColorIndex = 34 Case "Call Off": .Font.ColorIndex = 44 Case "Holiday": .Font.ColorIndex = 43 Case "Meeting": .Font.ColorIndex = 35 Case "Project": .Font.ColorIndex = 36 Case "Training": .Font.ColorIndex = 37 End Select End With CleanUp: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Fleone" wrote in message ... Ok, I just got back on the forum here, and saw your response Patrick, I will try this and see what it does. In the meantime, I found this buried in another workbook that I have been working with and made some tweaks and it seems to be working. I have a question about it though. Private Sub Worksheet_change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("C4:U13")) Is Nothing _ Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Lunch": .Interior.ColorIndex = 6 Case "Off": .Interior.ColorIndex = 0 Case "Vacation": .Interior.ColorIndex = 34 Case "Call Off": .Interior.ColorIndex = 44 Case "Holiday": .Interior.ColorIndex = 43 Case "Meeting": .Interior.ColorIndex = 35 Case "Project": .Interior.ColorIndex = 36 Case "Training": .Interior.ColorIndex = 37 End Select End With CleanUp: Application.EnableEvents = True End Sub This is working GREAT for changing any of the cell colors in any worksheet that I add this code to, so as a method for defeating the conditional formatting limitation, it is doing exactly what I would expect. The next step in this would be to have the ability to affect a change to the font color in each of the cells according to their content. I have already tried making another entry in the worksheet identical to the one above, but changing Interior.ColorIndex to Font.ColorIndex. That doesn't work, I recieve an error on using an "ambigous" <sp? name. When I try to duplicate one of the lines, and making the same change, it doesn't work either. It has become a real head scratcher <G Any insight you can provide would be great! I will try the code that you have already provided. Thanks again! "Patrick Molloy" wrote: Using VBA. Add a standard code module and paste the following: Option Explicit Enum eColors Yellow = 10092543 Green = 13434828 Blue = 16777164 Grey = 12632256 Orange = 10079487 End Enum Sub MyConditionalFormatting(Target As Range) Dim cell As Range Dim clr As Long For Each cell In Target Select Case UCase(Trim(cell.Value)) Case "LUNCH": clr = eColors.Yellow Case "OFF": clr = eColors.Blue Case "HOLIDAY": clr = eColors.Grey Case "COURSE": clr = eColors.Orange Case Else: clr = eColors.Green End Select cell.Interior.Color = clr Next End Sub Sub test() MyConditionalFormatting Range("MyData") End Sub To test On a sheet, select a range, name it "MyData" type some values into some cells in th erange randomly, include: Holiday, Lunch, Off, Course run the Test procedure. This passes the range "MyData" to the procedure that then does the formatting. This example allows four colors, but you can see that its quite easy to adapt to use more colors. HTH Patrick Molloy Microaoft Excel MVP "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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to create "conditional formatting"
Patrick,
This works just as you described it would. Here is my only problem with it. (I am sure it is just because I really don't know what I am doing <G). The colors you specified in the eColor sub have a number associated with them that I am unfamiliar with. When I tried changing the color code assigned to Green to a color index number 4, the cells were all formatted in Black. Can you tell me where the number codes that you described can be found? If so, I can assign any number of colors to the eColor sub then correct? "Patrick Molloy" wrote: Using VBA. Add a standard code module and paste the following: Option Explicit Enum eColors Yellow = 10092543 Green = 13434828 Blue = 16777164 Grey = 12632256 Orange = 10079487 End Enum Sub MyConditionalFormatting(Target As Range) Dim cell As Range Dim clr As Long For Each cell In Target Select Case UCase(Trim(cell.Value)) Case "LUNCH": clr = eColors.Yellow Case "OFF": clr = eColors.Blue Case "HOLIDAY": clr = eColors.Grey Case "COURSE": clr = eColors.Orange Case Else: clr = eColors.Green End Select cell.Interior.Color = clr Next End Sub Sub test() MyConditionalFormatting Range("MyData") End Sub To test On a sheet, select a range, name it "MyData" type some values into some cells in th erange randomly, include: Holiday, Lunch, Off, Course run the Test procedure. This passes the range "MyData" to the procedure that then does the formatting. This example allows four colors, but you can see that its quite easy to adapt to use more colors. HTH Patrick Molloy Microaoft Excel MVP "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I create "blink" conditional formatting? | Excel Discussion (Misc queries) | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
Excel 2003: Conditional Formatting using "MIN" & "MAX" function | Excel Discussion (Misc queries) | |||
create links to check boxes marked "good" fair"and "bad" | Excel Worksheet Functions | |||
How do I create conditional "List of Values" in Excell? | Excel Discussion (Misc queries) |