Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 107
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I convert conditional formatting into explicit formatting? Patrick Harris Excel Discussion (Misc queries) 0 April 9th 09 12:00 AM
Conditional formatting--different formatting depending on cell con Tammy S. Excel Discussion (Misc queries) 3 March 30th 09 08:11 PM
Formatting cells in a column with conditional formatting? shamor Excel Discussion (Misc queries) 8 May 19th 08 10:11 PM
Protect Cell Formatting including Conditional Formatting Mick Jennings Excel Discussion (Misc queries) 5 November 13th 07 05:32 PM
Conditional Formatting that will display conditional data BrainFart Excel Worksheet Functions 1 September 13th 05 05:45 PM


All times are GMT +1. The time now is 12:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"