ncgloryholes
welcome to my space
Home

Conditional Formatting

January 7th, 2009 by jane
  • Dear all,

    Can anyone help?

    In the first four columns (A1,B1,C1,D1) of a worksheet in Excel I input before a race four horse racing tips which I call T1,T2,T3 and T4.

    I do so for every race so as to keep a record of all tips.

    In the next five columns (E1,F1,G1,H1,I1) and after each race is run, I input the first five horses which crossed the finish line. I equally do so for every race.

    What I would like is for the relevant cell or cells in columns (T1 to T4) to change colour and font only when the value(s) inputed in ( T1 to T4) equal(s) the value(s) in one of the 5 columns (E1 to I1).

    Example and Legend:

    If T1.value = E1. value then E1.value fontcolour = black and Backgroundcolour colour = Yellow

    If T1.value = F1. value then E1.value font colour= black and Backgroundcolour = red

    If T1.value = G1. value then E1.value fontcolour = black and Backgroundcolour = pink

    If T1.value = H1. value then E1.value fontcolour = black and Backgroundcolour = blue

    If T1.value = I1. value then E1.value fontcolour = black and Backgroundcolour = green.

    and repeat the above for T2,T3 and T4.

    I can curently do this using conditional formatting under Excel unfortunately I am limited to three conditions and in my case I can only change the colour of the first three horses crossing the finish line.

    I would really appreciate if someone could help as my skills in visual basic are limited.

    Thank you for taking the time to read.

    Regards.


  • Howdy. One approach is to put the following code into the worksheet module (right-click tab name, and choose View Code), then paste this into the window in VBE. This makes use of a hidden sheet (unhidden for this example) which contains a reference to each condition and color needed; it is named CFControl and contains the named range rngcolors. On worksheet Data, Column I contains the names to enter (just for reference when testing). Type one of the names in the region B2:G30 and you will see the color change.


    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Conditional Formatting for more than 3 conditions
    Dim rng As Range
    ' Target is a range::therefore,it can be more than one cell
    ' For example,,someone could delete the contents of a range,
    ' or someone could enter an array..
    Set rng = Intersect(Target, Range("B2:G30"))
    If rng Is Nothing Then
    Exit Sub
    Else
    Dim cl As Range
    For Each cl In rng
    On Error Resume Next
    ' — The preceding line doesn ’t change the cell ’s background
    ' — color if the cell ’s value is not found in the range
    ' — that we specified ((rngcolors).
    cl.Font.ColorIndex = _
    Application.WorksheetFunction.VLookup(cl.Value, _
    ThisWorkbook.Sheets("CFControl").Range("rngColors"), 2, False)
    If Err.Number <> 0 Then
    cl.Font.ColorIndex = xlNone
    End If
    Next cl
    End If
    End Sub



    You may have to adjust the references, etc. The advantage of this approach is that you make changes on CFControl worksheet, not in the VBA code.







    • #If you have any other info about this subject , Please add it free.#
    Your name:
    E-mail:
    Telphone:

    Your comments:


    If you have any other info about Conditional Formatting , Please add it free.

    Posted in ncgloryholes.com | edit

    Cycling Idol Faced With More Doping Allegations Los Angeles, Ca - Home and Garden


    Categories


    Archives


    Blogroll



    Meta