Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Fontsize to next column (2003)

    hello,
    does anyone know, how to set vba code to change the fontsize and coler of a column next to a column (right side, oder left side), where values are?
    i tried so in the attached file on Table 2 (Tabelle 2) but did not succed. is it a proper way at the select statement to use offset (0, -1) or (0, 1)??
    i have no idea to do so.
    stefan
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Fontsize to next column (2003)

    I'm afraid I don't understand the worksheet and the code. Can you explain in words what you want to accomplish?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Fontsize to next column (2003)

    hi hans,
    well, i have a column, where my values are and i want to change the fontsize of the data or text to the next column (row). - with conditional formatting, i can change the color of the column via formula. - to change the fontsize i neeed to use vba. for example: column B (values) and column C (or A) the text to change. I want to change the fontsize and the color of the corresponding cells.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Fontsize to next column (2003)

    Please explain in detail and exactly what you want to accomplish.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Fontsize to next column (2003)

    ok, i try it:
    i have a Legend to refer to: for example, the range is: D14:H18 in column D1418 are 5 n's - font: Webdings.
    in column e14:f18 are the minima to maxima of possible values (for conditional formats) - column E -minima, column F-maxima (E14:F14, E15:F15, and so on).
    in column G (G14:G18) i have the corresponding fontsizes as values - 10, 12, 14, 18, 20...
    in column H (H14:H18) i have the color code (taken from the color index).
    referring to this array i have 2 columns now: Column A, where only n's - font: Webdings- are and column B, where i have values.
    i want to change the n's in column A, corresponding to column B. - If a value in B is for example from 25000 to 50000, i want to change the corresponding n in size and color -corresponding to the Legend-data.
    stefan

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Fontsize to next column (2003)

    Your Tabelle2 worksheet is set up differently from your description. Could you either attach a workbook that conforms to the descrioption, or change the description to match the worksheet?

  7. #7
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Fontsize to next column (2003)

    hi hans,
    does the table, i attached, fit for you?
    regards,
    stefan
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Fontsize to next column (2003)

    Thanks. Use this code in the worksheet module of Tabelle2:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iColor As Integer
    Dim iSize As Integer
    Dim oCell As Range
    If Not Intersect(Target, Range("B1:B100")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("B1:B100"))
    iColor = Application.WorksheetFunction.VLookup(oCell, Range("E15:G19"), 3)
    iSize = Application.WorksheetFunction.VLookup(oCell, Range("E15:G19"), 2)
    With oCell.Offset(0, -1).Font
    .ColorIndex = iColor
    .Size = iSize
    End With
    Next oCell
    End If
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Fontsize to next column (2003)

    Edited by HansV to reduce very wide screenshot in size

    hi hans,
    thanks, again for your support, but the code stops at
    iColor = Application.WorksheetFunction.VLookup(oCell, Range("E15:G19"), 3)
    st.
    Attached Images Attached Images

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Fontsize to next column (2003)

    Perhaps your workbook is different from the one you posted? The code works there - see attached version (I added code to "reset" the font size and color if the cell in column B is cleared).
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Fontsize to next column (2003)

    hi hans,
    the only difference was my use of a maximum column. but now it works fine, many thanks..!!!!
    stef

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Fontsize to next column (2003)

    Sorry, I had forgotten that I removed the Maximum column. Also note that the lower bounds have been sorted in ascending order, and that they are all numbers, i.e. not a text such as ">250000".

Posting Permissions

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