Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to change color based on Text, NOT using Conditional Formatin
I'm looking to change the color of numbers based on the text beside the column.
Then I need to sum up the values based on those colors. I started things by using a Conditional Format, but that won't work. I believe I have the code for the rest of this project though. Also I'm looking through the Visual Basic Editor, there is a listing of all the functions you can use, but no real detail or what they do. Any information on where to find that? I could do this on my own if I knew more about the calls to Excel. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to change color based on Text, NOT using Conditional Formatin
Here's a little something to change color based on cell value in the
current selection. You can then write a little code to sum up the number of cells with an interior color index of a certain color. You could even do that with a UDF. Sub foofer() Dim r As Range For Each r In Selection Select Case r.Text Case "Fred" r.Interior.ColorIndex = 6 Case "Tom" r.Interior.ColorIndex = 4 Case Else r.Interior.ColorIndex = xlNone End Select Next r End Sub Mitch wrote: I'm looking to change the color of numbers based on the text beside the column. Then I need to sum up the values based on those colors. I started things by using a Conditional Format, but that won't work. I believe I have the code for the rest of this project though. Also I'm looking through the Visual Basic Editor, there is a listing of all the functions you can use, but no real detail or what they do. Any information on where to find that? I could do this on my own if I knew more about the calls to Excel. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to change color based on Text, NOT using Conditional Form
Thanks but that's a sub snippet. I'm not just there yet when it comes to
using the vba with Excel, I need it to automatically change the color of 2 cells, the CDN, and then the 33 value afterwards. I've already adjusted the code to change the Font color not the cell color. I just need to figure out how to get the neighbour cell to change. I might make it a function, and have it called in the neighbouring cell and have it change the 2 cells to the left of it. I get ?NAME with this. :( I figure I could call the function =ColChange(A1,A2) Except it doesn't work. Function ColChange(r1 As Range, r2 As Range) Dim r1 As Range Select Case r1.Text Case "USD" r1.Font.ColorIndex = 5 r2.Font.ColorIndex = 5 Case "CDN" r1.Font.ColorIndex = 3 r2.Font.ColorIndex = 3 Case Else r1.Font.ColorIndex = xlNone r2.Font.ColorIndex = xlNone End Select End Function "JW" wrote: Here's a little something to change color based on cell value in the current selection. You can then write a little code to sum up the number of cells with an interior color index of a certain color. You could even do that with a UDF. Sub foofer() Dim r As Range For Each r In Selection Select Case r.Text Case "Fred" r.Interior.ColorIndex = 6 Case "Tom" r.Interior.ColorIndex = 4 Case Else r.Interior.ColorIndex = xlNone End Select Next r End Sub Mitch wrote: I'm looking to change the color of numbers based on the text beside the column. Then I need to sum up the values based on those colors. I started things by using a Conditional Format, but that won't work. I believe I have the code for the rest of this project though. Also I'm looking through the Visual Basic Editor, there is a listing of all the functions you can use, but no real detail or what they do. Any information on where to find that? I could do this on my own if I knew more about the calls to Excel. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to change color based on Text, NOT using Conditional Form
Ok, so my UDF isn't even working. I tried using yours, thing is I need 2
cells. I'm not familar enough with this to have the system move a cell over and apply the same formating. I always get $value when I do the UDF in the neighbouring cell. I'm getting the impression I'm going about some of this the wrong way. Do you have any good reference sites that have additional examples? (or know how to include 2 cells? Like the current cell and the left to the current one?) "JW" wrote: Here's a little something to change color based on cell value in the current selection. You can then write a little code to sum up the number of cells with an interior color index of a certain color. You could even do that with a UDF. Sub foofer() Dim r As Range For Each r In Selection Select Case r.Text Case "Fred" r.Interior.ColorIndex = 6 Case "Tom" r.Interior.ColorIndex = 4 Case Else r.Interior.ColorIndex = xlNone End Select Next r End Sub Mitch wrote: I'm looking to change the color of numbers based on the text beside the column. Then I need to sum up the values based on those colors. I started things by using a Conditional Format, but that won't work. I believe I have the code for the rest of this project though. Also I'm looking through the Visual Basic Editor, there is a listing of all the functions you can use, but no real detail or what they do. Any information on where to find that? I could do this on my own if I knew more about the calls to Excel. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Font Color Based on Conditional Formatting | Excel Worksheet Functions | |||
Conditional format: change row color based on cell value | Excel Discussion (Misc queries) | |||
ARE THERE CONDITIONAL FORMULAS TO CHANGE TEXT COLOR? | Excel Worksheet Functions | |||
How to code so cells or text automatically change color based on . | Excel Worksheet Functions | |||
change text color based on logical test | Excel Worksheet Functions |