PC Review


Reply
Thread Tools Rate Thread

Excel conditional formatting - how to make exceptions?

 
 
Highlander
Guest
Posts: n/a
 
      19th Jun 2007
Hello all.

I've got a VBScript that creates an XLS file. In it I want to
implement conditional formatting; where if a cell value in Column C is
greater than 50000, then that cell font will be changed to bold and
red.

Recording a macro gave me this:

Sub Bold_Red()
Columns("C:C").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="50000"
With Selection.FormatConditions(1).Font
.Bold = True
.ColorIndex = 3
.Italic = False
End With
End Sub

Then, after adding these Constants:

Const xlCellValue = 1
Const xlGreater = 5

I transposed the above macro into the following code:

'~~ Insert Bold and Red Font for any value
'~~ greater than 50000 in Column C
objExcel.Range("C:C").Select
objExcel.Selection.FormatConditions.Delete
objExcel.Selection.FormatConditions.Add xlCellValue, xlGreater,
"50000"
With objExcel.Selection.FormatConditions(1).Font
.Bold = True
.ColorIndex = 3 'Red
.Italic = False
End With

The code works fine except for one problem. Some of the cells in
Column C contain text, not a number, and the text is getting changed
to the Bold and Red font. I want the font to change only for cells
that contain a number, not text. How do I exclude the text cells from
the conditional formatting?

Any suggestions would be greatly appreciated. Thanks!

- Dave

 
Reply With Quote
 
 
 
 
=?Utf-8?B?c3RldmVfZG9j?=
Guest
Posts: n/a
 
      19th Jun 2007
Possible solution
Assumes there are no blank cells


Sub Test50000()

Dim wb as Workbook
Dim ws as Worksheet
Dim rg as Range

Set wb = ThisWorkbook
Set ws = wb.WorkSheets("yourWorksheetName")
Set rg = ws.Range("C1") 'as example

Do Until IsEmpty(rg)
If rg.Value > 50000 Then 'assuming that formatted as general
rg.Font.Bold = True
Rg.Font.ColouriNdex = 3
Set rg = rg.Offset(1,0)
Else
Set rg = rg.Offset(1,0)
End If
Loop

End Sub

> Hello all.
>
> I've got a VBScript that creates an XLS file. In it I want to
> implement conditional formatting; where if a cell value in Column C is
> greater than 50000, then that cell font will be changed to bold and
> red.
>
> Recording a macro gave me this:
>
> Sub Bold_Red()
> Columns("C:C").Select
> Selection.FormatConditions.Delete
> Selection.FormatConditions.Add Type:=xlCellValue,
> Operator:=xlGreater, _
> Formula1:="50000"
> With Selection.FormatConditions(1).Font
> .Bold = True
> .ColorIndex = 3
> .Italic = False
> End With
> End Sub
>
> Then, after adding these Constants:
>
> Const xlCellValue = 1
> Const xlGreater = 5
>
> I transposed the above macro into the following code:
>
> '~~ Insert Bold and Red Font for any value
> '~~ greater than 50000 in Column C
> objExcel.Range("C:C").Select
> objExcel.Selection.FormatConditions.Delete
> objExcel.Selection.FormatConditions.Add xlCellValue, xlGreater,
> "50000"
> With objExcel.Selection.FormatConditions(1).Font
> .Bold = True
> .ColorIndex = 3 'Red
> .Italic = False
> End With
>
> The code works fine except for one problem. Some of the cells in
> Column C contain text, not a number, and the text is getting changed
> to the Bold and Red font. I want the font to change only for cells
> that contain a number, not text. How do I exclude the text cells from
> the conditional formatting?
>
> Any suggestions would be greatly appreciated. Thanks!
>
> - Dave
>
>

 
Reply With Quote
 
 
 
 
=?Utf-8?B?VG9tIE9naWx2eQ==?=
Guest
Posts: n/a
 
      19th Jun 2007
objExcel.Range("C:C").specialCells(xlConstants,xlNumbers).Select

change xlConstants to xlformulas if the numbers are produced by formula.

If it is a mix, do it twice - once with xlconstants and once with xlformulas
- or form a union

--
Regards,
Tom Ogilvy


"Highlander" wrote:

> Hello all.
>
> I've got a VBScript that creates an XLS file. In it I want to
> implement conditional formatting; where if a cell value in Column C is
> greater than 50000, then that cell font will be changed to bold and
> red.
>
> Recording a macro gave me this:
>
> Sub Bold_Red()
> Columns("C:C").Select
> Selection.FormatConditions.Delete
> Selection.FormatConditions.Add Type:=xlCellValue,
> Operator:=xlGreater, _
> Formula1:="50000"
> With Selection.FormatConditions(1).Font
> .Bold = True
> .ColorIndex = 3
> .Italic = False
> End With
> End Sub
>
> Then, after adding these Constants:
>
> Const xlCellValue = 1
> Const xlGreater = 5
>
> I transposed the above macro into the following code:
>
> '~~ Insert Bold and Red Font for any value
> '~~ greater than 50000 in Column C
> objExcel.Range("C:C").Select
> objExcel.Selection.FormatConditions.Delete
> objExcel.Selection.FormatConditions.Add xlCellValue, xlGreater,
> "50000"
> With objExcel.Selection.FormatConditions(1).Font
> .Bold = True
> .ColorIndex = 3 'Red
> .Italic = False
> End With
>
> The code works fine except for one problem. Some of the cells in
> Column C contain text, not a number, and the text is getting changed
> to the Bold and Red font. I want the font to change only for cells
> that contain a number, not text. How do I exclude the text cells from
> the conditional formatting?
>
> Any suggestions would be greatly appreciated. Thanks!
>
> - Dave
>
>

 
Reply With Quote
 
Highlander
Guest
Posts: n/a
 
      19th Jun 2007
On Jun 19, 9:53 am, Tom Ogilvy <(E-Mail Removed)>
wrote:
> objExcel.Range("C:C").specialCells(xlConstants,xlNumbers).Select
>
> change xlConstants to xlformulas if the numbers are produced by formula.
>
> If it is a mix, do it twice - once with xlconstants and once with xlformulas
> - or form a union
>
> --
> Regards,
> Tom Ogilvy
>
>


Tom I tried your suggestion but couldn't get it to work.

Using a variation on steve_doc's suggestion, I solved my problem. I
did have to list the specific cells in the range; if I used the entire
column C it would be an infinite FOR loop.

Here's my corrected code that works:

'~~ Insert Bold and Red Font for any value
'~~ greater than 50000 in Column C
Set objRange = objExcel.Range("C3:C13")
For Each item in objRange.Cells
v = item.Value
Select Case True
Case IsNumeric(v) ' Determine if cell value is a number
IF v > 50000 Then
With item.Cells.Font
.Bold = True
.ColorIndex = 3 ' Red
.Italic = False
End With
End IF
End Select
Next

Thanks to both of you for responding!

- Dave

 
Reply With Quote
 
=?Utf-8?B?VG9tIE9naWx2eQ==?=
Guest
Posts: n/a
 
      19th Jun 2007
If you want to run the macro everytime you need to color the cells - then
that is the easiest - but then why did you attempt to use conditional
formatting.

This worked fine for me by the way. (just so you don't lead others astray).

Sub BBB()
'~~ Insert Bold and Red Font for any value
'~~ greater than 50000 in Column C
Set objExcel = Application
objExcel.Range("C:C").Select
objExcel.Selection.FormatConditions.Delete
objExcel.Range("C:C").SpecialCells(xlConstants, xlNumbers).Select
objExcel.Selection.FormatConditions.Add xlCellValue, xlGreater, 50000
With objExcel.Selection.FormatConditions(1).Font
.Bold = True
.ColorIndex = 3 'Red
.Italic = False
End With

End Sub

Just like you had to define the constant arguments for the conditional
formatting, you would have to define them for the SpecialCells as well - but
seemed like you would know that since you already did it for data validation.


--
Regards,
Tom Ogilvy


"Highlander" wrote:

> On Jun 19, 9:53 am, Tom Ogilvy <(E-Mail Removed)>
> wrote:
> > objExcel.Range("C:C").specialCells(xlConstants,xlNumbers).Select
> >
> > change xlConstants to xlformulas if the numbers are produced by formula.
> >
> > If it is a mix, do it twice - once with xlconstants and once with xlformulas
> > - or form a union
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >

>
> Tom I tried your suggestion but couldn't get it to work.
>
> Using a variation on steve_doc's suggestion, I solved my problem. I
> did have to list the specific cells in the range; if I used the entire
> column C it would be an infinite FOR loop.
>
> Here's my corrected code that works:
>
> '~~ Insert Bold and Red Font for any value
> '~~ greater than 50000 in Column C
> Set objRange = objExcel.Range("C3:C13")
> For Each item in objRange.Cells
> v = item.Value
> Select Case True
> Case IsNumeric(v) ' Determine if cell value is a number
> IF v > 50000 Then
> With item.Cells.Font
> .Bold = True
> .ColorIndex = 3 ' Red
> .Italic = False
> End With
> End IF
> End Select
> Next
>
> Thanks to both of you for responding!
>
> - Dave
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel: How to make conditional formatting work with hyperlinks =?Utf-8?B?Q2hhZEJlbGxhbg==?= Microsoft Excel Misc 3 25th May 2007 02:18 PM
Conditional Formatting - Can I select a select with Conditional Formatting? Greegan Microsoft Excel Worksheet Functions 5 31st Oct 2005 03:19 PM
How to Make a Cell Flash in Excel using conditional formatting =?Utf-8?B?YWJvZWhuZW4=?= Microsoft Excel Misc 1 23rd Apr 2005 01:53 AM
Make a greenbar page in Excel using Conditional Formatting dsample Microsoft Excel Discussion 1 13th Apr 2004 05:46 PM
Conditional, Conditional Formatting John Meyer Microsoft Excel Discussion 8 21st Dec 2003 09:37 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:43 AM.