Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Highlighting column and row intersection cells?

This is a discussion on Highlighting column and row intersection cells? within the Excel Questions forums, part of the Question Forums category; Hi everybody, Any code or formula to highlight the intersected cells between a column and a row for any selected ...

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Posts
    508

    Default Highlighting column and row intersection cells?

    Hi everybody,

    Any code or formula to highlight the intersected cells between a column and a row for any selected cell ?

    Ex: if i select E25 the cells of column E1:E25 as well as the cells of Row A25:E25 to be highlighted in a certain color , and so on for any selected cell..

    Thank you much for your help .
    He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever.

  2. #2
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,478

    Default Re: Highlighting column and row intersection cells?

    maybe this?

    Code:
    Sub test()
    Range(Cells(1, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column)).Interior.Color = vbYellow
    Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, ActiveCell.Column)).Interior.Color = vbYellow
    End Sub

  3. #3
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,829

    Default Re: Highlighting column and row intersection cells?

    Hi,

    Do you mean that the "color bands" are moving depending on the selection. Is it a way to see more clearly what cell is selected?

    Then you can do a search for "color banding".

    There are different techniques, which all have their advantages and limitations. I prefer to use conditional format. You can only apply it when you do not need conditional format for other purposes.

    Select range
    Formula for conditional format
    =OR(ROW()=CELL("row"),COLUMN()=CELL("col"))

    in the sheetmodule
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Application.ScreenUpdating = True
    End Sub
    This will highlight more than you asked, but you can take this as a start.

    kind regards,
    Erik
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

  4. #4
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,478

    Default Re: Highlighting column and row intersection cells?

    here's code that will change it automatically...you would put this in the sheet code (ie right click worksheet tab, view code)...

    paste this in...this will change the highlighting based upon cell you select automatically...
    just change
    Code:
    Set Rng = Range("A1:Z100")
    to whatever range you want it to process...

    Code:
    Private Sub Worksheet_SelectionChange(ByVal target As Range)
     If target.Cells.Count > 1 Then Exit Sub
       Set Rng = Range("A1:Z100")
       If Not Intersect(target, Rng) Is Nothing Then
       Rng.Interior.Pattern = xlNone
       Range(Cells(1, target.Column), Cells(target.Row, target.Column)).Interior.Color = vbYellow
       Range(Cells(target.Row, 1), Cells(target.Row, target.Column)).Interior.Color = vbYellow
      End If
    End Sub

  5. #5
    Board Regular
    Join Date
    Feb 2003
    Posts
    508

    Default Re: Highlighting column and row intersection cells?

    Dear Erik ,
    The code with the conditional formatting is very close of what i need and as you said it is more of what i asked, it highlights more lower rows and more columns to the right. Thank you.


    The code of TheNoocH worked great ! can i change color to whatever i need,what are the colors name ?

    Thank you all guys !
    He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever.

  6. #6
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,478

    Default Re: Highlighting column and row intersection cells?

    great glad it worked...
    you can replace vbYellow with any of these color constants
    vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, vbWhite

    or you can change .interior.color to .interior.colorindex = # where # is any of the below numbers

    ******** ******************** ************************************************************************>
    Microsoft Excel - UDF-Color.xls___Running: 12.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    1
    1Black1 37Pale Blue37
    2
    2White2 38Rose38
    3
    3Red3 39Lavender39
    4
    4Bright Green4 40Tan40
    5
    5Blue5 41Light Blue41
    6
    6Yellow6 42Aqua42
    7
    7Pink7 43Lime43
    8
    8Turquoise8 44Gold44
    9
    9Dark Red9 45Light Orange45
    10
    10Green10 46Orange46
    11
    11Dark Blue11 47Blue-Gray47
    12
    12Dark Yellow12 48Gray-40%48
    13
    13Violet13 49Dark Teal49
    14
    14Teal14 50Sea Green50
    15
    15Gray-25%15 51Dark Green51
    16
    16Gray-50%16 52Olive Green52
    17
    33Sky Blue33 53Brown53
    18
    34Light Turquoise34 54Plum54
    19
    35Light Green35 55Indigo55
    20
    36Light Yellow36 56Gray-80%56
    CellColor

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  7. #7
    Board Regular
    Join Date
    Feb 2003
    Posts
    508

    Default Re: Highlighting column and row intersection cells?

    Milion thanks !
    He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever.

  8. #8
    Board Regular
    Join Date
    Feb 2003
    Posts
    508

    Default Re: Highlighting column and row intersection cells?

    Oopss ! i changed the vbYellow to vbGray-40% and gave me a light blue color!
    where is the problem ?
    He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever.

  9. #9
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,478

    Default Re: Highlighting column and row intersection cells?

    you can only replace vbYellow with one of the color constants from my prior post...

    in order to use Gray-40% you have to use color index...

    change
    Code:
       Range(Cells(1, target.Column), Cells(target.Row, target.Column)).Interior.Color = vbYellow
       Range(Cells(target.Row, 1), Cells(target.Row, target.Column)).Interior.Color = vbYellow
    to

    Code:
       Range(Cells(1, target.Column), Cells(target.Row, target.Column)).Interior.ColorIndex = 48
       Range(Cells(target.Row, 1), Cells(target.Row, target.Column)).Interior.ColorIndex = 48

  10. #10
    Board Regular
    Join Date
    Feb 2003
    Posts
    508

    Default Re: Highlighting column and row intersection cells?

    thats very clear now , thank you much .
    He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever.

Page 1 of 4 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com