LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 78
Default Conditional Formatting Code

Need help creating the code for adding conditional formatting to cell ranges €¦

For range (Axx:Ryy), I need the following conditional formatting conditions
created:
1 €“ Shade row grey if column "K" value = "Completed"
2 €“ Shade row grey if column "K" value = "Cancelled"

In addition, column "I" needs to have a 3rd condition:
3 €“ Make column "I" value red font if condition met (formula =$Jx < 3)

I recorded a macro for creating the conditions & got the following code that
I can copy to cells. Just not sure how to generalize it so it will not
reference cell $K701 below (it needs to reference columns K & J but apply to
all rows in the range):

Range(strI_Range).Select
Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$K701 = ""Completed"""
Selection.FormatConditions(1).Interior.ColorIndex = 33


Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$K701 = ""Cancelled"""
Selection.FormatConditions(2).Interior.ColorIndex = 33

' 3rd condition in cond fmtg sub for column I only
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J701 < 3"
With Selection.FormatConditions(3).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With

If there is a more elegant way to do this, please let me know. Right now I
have this as 2 subs (one for col I & the other for the other cells in the
range).

I also have a Click Event in Sheet 1 that shades a column for different
values. Not sure how to combine them (if that's possible and/or if it is
worth doing).

Thanks for your help!

 
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
Conditional Formatting vb code beverlydawn Excel Programming 1 March 5th 08 07:41 PM
Conditional Formatting using code Ayo Excel Discussion (Misc queries) 5 February 29th 08 01:09 PM
Conditional Formatting to VBA code Dean P. Excel Programming 1 December 18th 07 05:03 PM
Need help to changes a conditional formatting code.... BeSmart Excel Programming 7 September 14th 04 01:27 AM
Conditional Formatting using VBA Code andibevan Excel Programming 2 July 9th 04 04:43 PM


All times are GMT +1. The time now is 07:41 AM.

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

About Us

"It's about Microsoft Excel"