| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Conditional Formatting
Hello.
Is there a way to bypass Conditional Formatting if 1) you need more than three different formats or 2) you have a problem with people cutting/pasting information which removes the conditional formatting? I have a spreadsheet setup with conditional formatting. If cell C4 meets a specific criteria, then cells C3, C4, C5, C6 and C7 all are formatted as designed. I would like to find a formula/VBA script that I could setup and use that would accomplish the same task, but not use Conditional Formatting. Any and all help would be greatly appreciated! I am pulling my hair out!! Thanks. Bruise |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alternative to Conditional Formatting
You can change the colour - or indeed any defined formattting of a
cell - by using a routine called by the Workbook_SheetCalculate, Workbook_SheetSelectionChange or similar events. Just use an IF statement or any other logical test that results in a change in formatting. For example: If Cells(2,3).Value = <some condition Then Cells(2,3).Interior.ColorIndex = 20 End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alternative to Conditional Formatting
see if you can adapt this. if c3 is a number, it will color c3:c7 green.
test it out. right click the sheet name, choose view code and paste it on the sheet code page if you only want it on one sheet Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Range("c3:c7") If Application.IsNumber(Range("c3")) Then .Interior.ColorIndex = 35 Else .Interior.ColorIndex = 0 End If End With End Sub -- Gary "Bruise" wrote in message ... Hello. Is there a way to bypass Conditional Formatting if 1) you need more than three different formats or 2) you have a problem with people cutting/pasting information which removes the conditional formatting? I have a spreadsheet setup with conditional formatting. If cell C4 meets a specific criteria, then cells C3, C4, C5, C6 and C7 all are formatted as designed. I would like to find a formula/VBA script that I could setup and use that would accomplish the same task, but not use Conditional Formatting. Any and all help would be greatly appreciated! I am pulling my hair out!! Thanks. Bruise |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alternative to Conditional Formatting
Thanks, Gary. This does work. If I wanted the value to be a specific
text (i.e., "SOLD", or "EMPTY"), the IsNumber reference won't work. What would be the one for text and how would I write that one? Thanks again. This helps out a ton! Mark Gary Keramidas wrote: see if you can adapt this. if c3 is a number, it will color c3:c7 green. test it out. right click the sheet name, choose view code and paste it on the sheet code page if you only want it on one sheet Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Range("c3:c7") If Application.IsNumber(Range("c3")) Then .Interior.ColorIndex = 35 Else .Interior.ColorIndex = 0 End If End With End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alternative to Conditional Formatting
give this a try, adjust the range to your liking
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count = 1 Then If Not Intersect(Target, Range("C1:C17")) Is Nothing Then If UCase(Target) = "EMPTY" Or UCase(Target.Value) = "SOLD" Then Target.Interior.ColorIndex = 35 Else Target.Interior.ColorIndex = 0 End If End If End If End Sub -- Gary "Bruise" wrote in message ... Thanks, Gary. This does work. If I wanted the value to be a specific text (i.e., "SOLD", or "EMPTY"), the IsNumber reference won't work. What would be the one for text and how would I write that one? Thanks again. This helps out a ton! Mark Gary Keramidas wrote: see if you can adapt this. if c3 is a number, it will color c3:c7 green. test it out. right click the sheet name, choose view code and paste it on the sheet code page if you only want it on one sheet Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Range("c3:c7") If Application.IsNumber(Range("c3")) Then .Interior.ColorIndex = 35 Else .Interior.ColorIndex = 0 End If End With End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alternative to Conditional Formatting
Here is a subsequent issue... using the VBA code If UCase(Target.Value) = "text" Then Target.Interior.ColorIndex = 23 Whenever I press the DEL key to delete the content of the cell, a 13 runtime error pops up. How can I make sure this does not occur? -- simtug ------------------------------------------------------------------------ simtug's Profile: http://www.excelforum.com/member.php...o&userid=28255 View this thread: http://www.excelforum.com/showthread...hreadid=477742 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alternative to Conditional Formatting
can you post all of the code. the word text in your example needs to be
capitalized for it to work, but that won't explain the error you're getting. what it's doing is comparing the upper case value of the cell to the word text and since text is lower case, it will never work. ucase is used so that no matter how the word text is typed into the cell, it will always evaluate to upper case, so ="TEXT" is how it should read you could also change it to if lcase(target.value) and leave ="text" how it is. same thing. -- Gary "simtug" wrote in message ... Here is a subsequent issue... using the VBA code If UCase(Target.Value) = "text" Then Target.Interior.ColorIndex = 23 Whenever I press the DEL key to delete the content of the cell, a 13 runtime error pops up. How can I make sure this does not occur? -- simtug ------------------------------------------------------------------------ simtug's Profile: http://www.excelforum.com/member.php...o&userid=28255 View this thread: http://www.excelforum.com/showthread...hreadid=477742 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alternative to Conditional Formatting
Hi, Gary, my aim is to change the color of cells depending on the tex typed in by the end user. The conditional formatting option is no feasible. The VBA code I have used goes as follows: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("U4")) Is Nothing Then If UCase(Target.Value) = "0" Then Target.Interior.ColorIndex = 43 Else If UCase(Target.Value) = "A1" Then Target.Interior.ColorIndex = 17 Else If UCase(Target.Value) = "A2" Then Target.Interior.ColorIndex = 23 Else If UCase(Target.Value) = "B1" Then Target.Interior.ColorIndex = 44 Else If UCase(Target.Value) = "B2" Then Target.Interior.ColorIndex = 46 Else If UCase(Target.Value) = "C1" Then Target.Interior.ColorIndex = 39 Else If UCase(Target.Value) = "C2" Then Target.Interior.ColorIndex = 47 Else End If End If End If End If End If End If End If End If End Sub When I place the cursor in the U4 cell and I change the previou inserted text with a different one no problem, but when I want t delete the previous typed text by pressing del DEL key (which i something the end user is routinely doing) I get a runtime error (13 alerting about a non-corrisponding type error. Any help how to avoid the error window to pop up? Thank you very much for any help! Simon -- simtu ----------------------------------------------------------------------- simtug's Profile: http://www.excelforum.com/member.php...fo&userid=2825 View this thread: http://www.excelforum.com/showthread.php?threadid=47774 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alternative to Conditional Formatting
i don't get any error when i delete the contents of u4.
i did add a line to change the background back if the value isn't one of the one's you're testing for. i don't know why you get a runtime error. Target.Interior.ColorIndex = 47 Else Target.Interior.ColorIndex = 0 End If -- Gary "simtug" wrote in message ... Hi, Gary, my aim is to change the color of cells depending on the text typed in by the end user. The conditional formatting option is not feasible. The VBA code I have used goes as follows: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("U4")) Is Nothing Then If UCase(Target.Value) = "0" Then Target.Interior.ColorIndex = 43 Else If UCase(Target.Value) = "A1" Then Target.Interior.ColorIndex = 17 Else If UCase(Target.Value) = "A2" Then Target.Interior.ColorIndex = 23 Else If UCase(Target.Value) = "B1" Then Target.Interior.ColorIndex = 44 Else If UCase(Target.Value) = "B2" Then Target.Interior.ColorIndex = 46 Else If UCase(Target.Value) = "C1" Then Target.Interior.ColorIndex = 39 Else If UCase(Target.Value) = "C2" Then Target.Interior.ColorIndex = 47 Else End If End If End If End If End If End If End If End If End Sub When I place the cursor in the U4 cell and I change the previous inserted text with a different one no problem, but when I want to delete the previous typed text by pressing del DEL key (which is something the end user is routinely doing) I get a runtime error (13) alerting about a non-corrisponding type error. Any help how to avoid the error window to pop up? Thank you very much for any help! Simone -- simtug ------------------------------------------------------------------------ simtug's Profile: http://www.excelforum.com/member.php...o&userid=28255 View this thread: http://www.excelforum.com/showthread...hreadid=477742 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alternative to Conditional Formatting
Hi Simtug,
Like Gary, I could not replicate your error. Which specific line of code is highlighted when the error occurs? --- Regards, Norman "simtug" wrote in message ... Hi, Gary, my aim is to change the color of cells depending on the text typed in by the end user. The conditional formatting option is not feasible. The VBA code I have used goes as follows: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("U4")) Is Nothing Then If UCase(Target.Value) = "0" Then Target.Interior.ColorIndex = 43 Else If UCase(Target.Value) = "A1" Then Target.Interior.ColorIndex = 17 Else If UCase(Target.Value) = "A2" Then Target.Interior.ColorIndex = 23 Else If UCase(Target.Value) = "B1" Then Target.Interior.ColorIndex = 44 Else If UCase(Target.Value) = "B2" Then Target.Interior.ColorIndex = 46 Else If UCase(Target.Value) = "C1" Then Target.Interior.ColorIndex = 39 Else If UCase(Target.Value) = "C2" Then Target.Interior.ColorIndex = 47 Else End If End If End If End If End If End If End If End If End Sub When I place the cursor in the U4 cell and I change the previous inserted text with a different one no problem, but when I want to delete the previous typed text by pressing del DEL key (which is something the end user is routinely doing) I get a runtime error (13) alerting about a non-corrisponding type error. Any help how to avoid the error window to pop up? Thank you very much for any help! Simone -- simtug ------------------------------------------------------------------------ simtug's Profile: http://www.excelforum.com/member.php...o&userid=28255 View this thread: http://www.excelforum.com/showthread...hreadid=477742 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
| Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
| Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
| Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Linear Mode
