Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Howdy,

    I have created a conditional formatting for an excel 2003 based on using 'Case' conditions (to allow me to have more than 3 conditional formats). Unfortunately my search of the threads hasn't pointed me to a method to shade a range of cells based on the entry in a particular cell in the same row. The example I have attached is a task list.

    Based on the status (column G) entry, I would like to shade the same row between cells A and I (see attached example).

    Also, I would be keen to understand how I can run a 'macro' to invoke the code if I enter the code on a data spreadsheet after the data is entered - at the moment the code only changes the cell shading if the code is in the spreadsheet prior to the data entry.

    Apologies if this question is 'very simple'. The task list spreadsheet is attached.


    Here's the code I have used:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer
    If Not Intersect(Target, Range("G2:G100")) Is Nothing Then
    Select Case Target
    Case "Open"
    icolor = 36
    Case "Done"
    icolor = 35
    Case "Closed"
    icolor = 15
    Case Else
    icolor = 2
    End Select
    Target.Interior.ColorIndex = icolor
    End If
    End Sub
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Change the line that sets the color to

    Range("A" & Target.Row & ":I" & Target.Row).Interior.ColorIndex = icolor

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='CPD-CB' post='789299' date='17-Aug-2009 09:04']Also, I would be keen to understand how I can run a 'macro' to invoke the code if I enter the code on a data spreadsheet after the data is entered ...[/quote]

    Further to Hans' response. Add the following Refresh procedure to a standard module and run it after activating the data sheet.
    Code:
    Public Sub Refresh()
     Dim i As Long
    	 For i = 2 To 2000
    		 Range("G" & i).Value = Range("G" & i).Value
    	 Next i
     End Sub
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •