Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UFD to change color based on string
ok, I keep getting =Name? and I'm not sure if this is even going to work.
Any help would be appreciated. Tried figuring this out on my own and not having much luck. Basically Text in A1 has USD or CDN or something else, if USD change color of A1 to 5 and the neighbouring cells color changes to 5. (The text not the background). Function ColChange(r1 As Variant) Select Case r1.Text Case "USD" r1.Font.ColorIndex = 5 r1.Offset(1, 2).Font.ColorIndex = 5 Case "CAD" r1.Font.ColorIndex = 3 r1.Offset(1, 2).Font.ColorIndex = 3 Case Else r1.Font.ColorIndex = xlNone r1.Offset(1, 2).Font.ColorIndex = 5 End Select End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UFD to change color based on string
I had the conditional formating doing this before, but you can't have excel
add based on color that way. Hmm.. What about using the USD or CDN then doing the offset thing to add according to country? That should work and I could still use the Conditional formating.. Hmm... The range varies each time, I could still use the function CASE, do a loop, hmm.. Any pit falls I might run into this this? "Tom Ogilvy" wrote: I assume you want to use this function in a worksheet. You probably have it in a sheet module. That will cause a #Name error - it should be in a general module all that said, You should use conditional formatting to do this. A UDF used in a worksheet can only return a value to a cell - it can't put values in other cells or change colors in any cell or otherwise alter the Excel environment. -- Regards, Tom Ogilvy "Mitch" wrote: ok, I keep getting =Name? and I'm not sure if this is even going to work. Any help would be appreciated. Tried figuring this out on my own and not having much luck. Basically Text in A1 has USD or CDN or something else, if USD change color of A1 to 5 and the neighbouring cells color changes to 5. (The text not the background). Function ColChange(r1 As Variant) Select Case r1.Text Case "USD" r1.Font.ColorIndex = 5 r1.Offset(1, 2).Font.ColorIndex = 5 Case "CAD" r1.Font.ColorIndex = 3 r1.Offset(1, 2).Font.ColorIndex = 3 Case Else r1.Font.ColorIndex = xlNone r1.Offset(1, 2).Font.ColorIndex = 5 End Select End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UFD to change color based on string
Please disregard, found the SUMIF function. Works like a charm.
"Tom Ogilvy" wrote: I assume you want to use this function in a worksheet. You probably have it in a sheet module. That will cause a #Name error - it should be in a general module all that said, You should use conditional formatting to do this. A UDF used in a worksheet can only return a value to a cell - it can't put values in other cells or change colors in any cell or otherwise alter the Excel environment. -- Regards, Tom Ogilvy "Mitch" wrote: ok, I keep getting =Name? and I'm not sure if this is even going to work. Any help would be appreciated. Tried figuring this out on my own and not having much luck. Basically Text in A1 has USD or CDN or something else, if USD change color of A1 to 5 and the neighbouring cells color changes to 5. (The text not the background). Function ColChange(r1 As Variant) Select Case r1.Text Case "USD" r1.Font.ColorIndex = 5 r1.Offset(1, 2).Font.ColorIndex = 5 Case "CAD" r1.Font.ColorIndex = 3 r1.Offset(1, 2).Font.ColorIndex = 3 Case Else r1.Font.ColorIndex = xlNone r1.Offset(1, 2).Font.ColorIndex = 5 End Select End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change color within string? | Excel Discussion (Misc queries) | |||
Change tab color based on current color of a cell | Excel Discussion (Misc queries) | |||
UFD to change color based on string | Excel Programming | |||
change fill color of a range of cells based on color of a cell? | Excel Programming | |||
Browse Forms Controls and change TextBox color based on cell color | Excel Programming |