Error with simple code at "Select Case ActiveCell.Offset(0, i).Value"

Question

Hi

I'm new to VBA and haven't coded for a few years but need to write some code that will allow me to ultimately conditionally format using a number of conditions.  I need to build this out more but to get started I have written some basic code for a one row range but am getting an error at this line "Select Case ActiveCell.Offset(0, i).Value" .  Apologies I know this is pretty basic but I'm just getting started and can't figure out if this is a syntax error or something more fundamental.

Could someone please help?

Thanks

---

Option Compare Text 'A=a, B=b, ... Z=z

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

Dim MyPlan As Range

Dim Cell As Range

Set MyPlan = Range("F6:AQ6")

For i = -41 To -3

    For Each Cell In MyPlan

                Select Case ActiveCell.Offset(0, i).Value

                Case "Phase 1"

                    Cell.Interior.ColorIndex = 3

                Case "Phase 2"

                    Cell.Interior.ColorIndex = 4

                Case "Phase 3"

                    Cell.Interior.ColorIndex = 5

                Case Else

                        Cell.Interior.ColorIndex = 2

                End Select

    Next

    Next

End Sub

Answer 1

Am 23.08.2010 15:52, schrieb CMW_001:

Thanks Andreas, that partially works.

I am glad to hear this. :-)

I  get a type mismatch error if I just enter the ranges as follows:

   StartDate = Range("D2:D20")

   EndDate = Range("E2:E20")

This is logical, I explain it once briefly.

When you make an assignment of one cell to one variable, then the contents of the cell is written into this variable.

The variable Startdate has the type "Date", so if we say

  StartDate = Range("A1")

the value of cell A1 will be written into the variable and thus becomes a date.

If you specify more than one cell like

  StartDate = Range("A1:A2")

you get a RTE 13 because the range returns an array of values and startdate can hold only one value.

Apologies but I'm not familiar with the Intersect function.  Does this recognise when there is an intersection at row level or do I need a loop of some kind?

You are right, we need a loop through the dates.

Intersect is a very powerful tool, try around a little bit with this macro, make some selections in a sheet, with not-related areas too and look what the MsgBox shows.

Sub Example_Intersect()

  Dim R As Range, C As Range

  Dim Msg As String

  If Not TypeOf Selection Is Range Then

    MsgBox "You did not select a range"

  Else

    Set R = Intersect(Selection, Rows(10))

    Set C = Intersect(Selection, Columns("F"))

    If R Is Nothing Then

      Msg = Msg & "Your selection intersects not Row 10"

    Else

      Msg = Msg & "Your selection intersects Row 10 at " & R.Address(0, 0)

    End If

    Msg = Msg & vbCrLf

    If C Is Nothing Then

      Msg = Msg & "and not Column F"

    Else

      Msg = Msg & "and intersects Column F at " & C.Address(0, 0)

    End If

    MsgBox Msg

  End If

End Sub

I think after a few tries, you'll understand what Intersect can do.

Back to your original question, the loop is very easy to program, but the many different colors are quite expensive... well not really, but I'm just lazy. ;-)

But we can do it with a nice trick very easy by simply reads the color of a cell. :-)

I simply take the cell containing the start date, if you don't like this feature or if you want a different cell and if you need help, just ask again.

Andreas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim R As Range, C As Range

  Dim FirstDate As Date, LastDate As Date

  Dim StartDate As Date, EndDate As Date

  Dim ClearLine As Boolean

  'If nothing of our interest is changed we're done

  Set Target = Intersect(Target, Range("F6:AQ20,D6:D20,E6:E20"))

  If Target Is Nothing Then Exit Sub

  'Don't flicker with the screen

  Application.ScreenUpdating = False

  'Walk trough the first column

  For Each R In Range("F6:F20")

    'Get the dates from this row

    FirstDate = R

    LastDate = Range("AQ" & R.Row)

    'Create the ganttline for each task

    ClearLine = True

    'Walk trough the dates

    For Each C In Range("D6:D20")

      'Get the start date from column D

      StartDate = C

      'Get the end date from column E same row as in Column D

      EndDate = C.Offset(0, 1)

      'Create the ganttline

      CreateGanttLine Range(R, Range("AQ" & R.Row)), _

          StartDate, EndDate, FirstDate, LastDate, C.Interior.ColorIndex, ClearLine

      'Overwrite the colored cells next time

      ClearLine = False

    Next

  Next

  'Show the result

  Application.ScreenUpdating = True

End Sub

Answer 2

Hi OssieMac

Ultimately I am trying to build project plan functionality with a Gantt view.

I currently have it working with conditional formatting but need to be able to format in more than 3 colours so trying to build functionality in VBA.

With the formula based version I am using Conditional formatting to create the Gantt lines using the following formula:

=AND(G$3>=$D6,G$3<=$E6)

where:

D6 is the task start date

E6 is the task end date

G3 is a date in a timeline running along the top

so it basically says if the relative value of the cell (in relation to timeline value on top in the same column) is between the task start date and end date fill format blue.

This rule is then set to a range of cells (F6 - AQ20) across the full timeline.

Unless I am missing an easier approach I need to be able to format the cell based on the value of cells to surrounding cells.

Hopefully this makes sense

Answer 3

Firstly you cannot use ActiveCell for a WorkSheet Change event if you want to address the cell that was changed. Reason is that depending on the users options, after changing the cell and pressing Enter the active cell might be the same cell that was changed or the cell to right, left, above or below depending on how the user has set the options. You need to use Target to address the changed cell.

Next maybe you can explain what it is that you are really trying to do. It appears to me that if a cell is changed then you want to color other cells in the assigned range depending on their value. Is this correct? If so then you don't need 2 loops. However, if I am not correct in my assumption then if you can explain what it is you want to achieve.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

Dim MyPlan As Range

Dim Cell As Range

Set MyPlan = Range("F6:AQ6")

  For Each Cell In MyPlan

    Select Case Cell.Value

      Case "Phase 1"

          Cell.Interior.ColorIndex = 3  'Red

      Case "Phase 2"

          Cell.Interior.ColorIndex = 4  'Green

      Case "Phase 3"

          Cell.Interior.ColorIndex = 5  'Blue

      Case Else

          Cell.Interior.ColorIndex = 2  'white

    End Select

  Next

End Sub

---

Regards, OssieMac

Answer 4

The problem is still the same using the Cell object as the first time through the loop Cell is referencing F6. The offset of -41 is way too far to the left of that cell.

Also Using cell will not make it dynamic as Cell will always be a cell in the range F6:AQ6. I'm guessing the dynamic part should make use of the Target object which is the cell that just changed. You then loop through testing values to the left of the Target. If the select case test is passed you set the relevant cell in the MyPlan range to a certain colour.

---

Cheers   www.andypope.info

Answer 5

The problem is still the same using the Cell object as the first time through the loop Cell is referencing F6. The offset of -41 is way too far to the left of that cell.

Also Using cell will not make it dynamic as Cell will always be a cell in the range F6:AQ6. I'm guessing the dynamic part should make use of the Target object which is the cell that just changed. You then loop through testing values to the left of the Target. If the select case test is passed you set the relevant cell in the MyPlan range to a certain colour.

---

Cheers   www.andypope.info