Posts by joelsmalley

    I have something similar also to go in module, rather than sheet vb:

    Sub Compare()

    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim temp1 As String
    Dim temp2 As String
    Dim temp3 As String
    Dim temp4 As String

    Dim n As Long
    Dim x As Long

    Set WS1 = Sheets("YTD CMA BOOKINGS")
    Set WS2 = Sheets("Jan CMA bookings")

    temp1 = WS1.Cells(1, 1)
    temp2 = WS1.Cells(1, 2)
    temp3 = WS2.Cells(1, 2)
    temp4 = WS2.Cells(1, 3)

    x = WS1.Cells(1, 1).End(xlDown).Row
    n = 1

    Do While temp1 <> ""

    For i = 1 To x

    temp1 = WS1.Cells(i, 1)
    temp2 = WS1.Cells(i, 2)

    temp3 = WS2.Cells(n, 2)
    temp4 = WS2.Cells(n, 3)

    If temp1 = temp3 And temp2 = temp4 Then
    WS1.Cells(i, 1).Interior.ColorIndex = 6
    WS1.Cells(i, 2).Interior.ColorIndex = 6
    WS2.Cells(n, 2).Interior.ColorIndex = 6
    WS2.Cells(n, 3).Interior.ColorIndex = 6

    End If
    n = n + 1
    temp1 = WS1.Cells(1, 1)
    If temp3 = "" Then End

    End Sub

    or try this:

    Sub time()

    While ActiveCell.Offset(1, 0) <> ""
    Call Insertrow
    End Sub

    Sub Insertrow()
    If Left(ActiveCell.Value, 2) <> Left(ActiveCell.Offset(1, 0).Value, 2) Then
    ActiveCell.Offset(1, 0).EntireRow.Insert
    ActiveCell.Offset(2, 0).EntireRow.Insert
    ActiveCell.Offset(3, 0).Activate
    ActiveCell.Offset(1, 0).Activate
    End If
    End Sub

    If you paste this in the worksheet VBA project, it should do your second attempt.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Sheets("Jan CMA bookings").Range("B122").Value = Sheets("YTD CMA BOOKINGS").Range("A62").Value _
    And Sheets("Jan CMA bookings").Range("C122").Value = Sheets("YTD CMA BOOKINGS").Range("B62").Value _
    Sheets("Jan CMA bookings").Range("B122").Interior.ColorIndex = 6
    Sheets("Jan CMA bookings").Range("C122").Interior.ColorIndex = 6
    Sheets("YTD CMA BOOKINGS").Range("A62").Interior.ColorIndex = 6
    Sheets("YTD CMA BOOKINGS").Range("B62").Interior.ColorIndex = 6

    Sheets("Jan CMA bookings").Range("B122").Interior.ColorIndex = 2
    Sheets("Jan CMA bookings").Range("C122").Interior.ColorIndex = 2
    Sheets("YTD CMA BOOKINGS").Range("A62").Interior.ColorIndex = 2
    Sheets("YTD CMA BOOKINGS").Range("B62").Interior.ColorIndex = 2
    End If
    End Sub

    I'm sure someone can come up with a better way though.


    This should work:

    Sub pearson()

    Dim temp As Long
    Dim wbBook As Workbook
    Dim wsActiveSheet As Worksheet, wsSheet As Worksheet

    Set wbBook = ActiveWorkbook
    Set wsActiveSheet = wbBook.ActiveSheet

    For Each wsSheet In wbBook.Worksheets
    If wsSheet.Name <> wsActiveSheet.Name Then
    wsSheet.Range("A448:IV450").Copy wsActiveSheet.Range("A1").Offset(temp, 0)
    temp = temp + 2
    End If
    Next wsSheet
    End Sub

    (Credit to XL Dennis - Never done anything like this before but cribbed some of your code to create a contents table and it works fine)


    This code does the copy and paste as you want it (I have tested it):

    Range(Range("here"), Range("here").Offset(0, Range("IV1").Column - Range("here").Column)).Copy _
    Range(Range("here"), Range("here").Offset(0, Range("IV1").Column - Range("here").Column)).Offset(1, 0)

    Is that all you need?


    This code will do the "IV" part of your problem, ie wherever you are in the spreadsheet, you will be taken to IV&row you are on:

    ActiveCell.Offset(0, Range("IV1").Column - ActiveCell.Column).Activate

    Not sure I fully understand what you are trying to achieve after that.

    Regards, Joel

    Here is some vba code to do it for you:

    Sub temp()

    With Worksheets("Sheet1").Range("A1")
    .Value = "abcdefghijklmnopqrstuvwxyz"
    .Characters(1, 10).Font.Bold = True
    .Characters(12, 3).Font.Name = "courier"
    End With

    End Sub

    Regards, Joel

    I have now! I was using "control source" in the userform properties window to link to the relevant cell on the spreadsheet. Doing it that way, the form writes the value of the formula into the spreadsheet cell as a value. Not very helpful. Your solution is perfect. thanks.


    Hi Piotr, I did it and it works with one small problem:

    Once I assign the control source of the text box in the form to the named cell range in the spreadsheet the form wants to write the value back to the spreadsheet when I close the form or move on. Obviously I have a formula in the cell that I want to keep (offset and match function to return the value in the first place).

    I fixed this problem by protecting the worksheet so the form can't write to it but sometimes get an error message from Excel complaining, presumeably because I stopped it doing this.

    Know of a better way to stop the form writing back to the spreadsheet?

    You have been most helpful so far. I appreciate it.


    I have data in a table in a spreadsheet. I have created a form with two combo boxes, each with data from the titles of the table (column and row). I have a textbox on the form and want to display the data in it which pertains to the cell located in the table which corresponds to the row and column label selected in the combo box. Is this possible?


    Hi All,

    I have an excel spreadsheet linked to a SQL server. The spreadsheet has financial market information coming into it in realtime. I have written a function to export this information to the database with a time stamp. How do I make the macro run at regualr intervals, say every five minutes please?