colour row
Results 1 to 4 of 4

Thread: colour row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2017
    Posts
    467
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Cool colour row

    I have worksheet A2:L50 which gets updated regular. When I insert ‘Yes’ in cell k2, I would like the ‘whole row (a2:l2) to fill colour to light blue.


    Would some kind person be able to help & sort for me?

    MTIA
    Trevor3007
    Thank you.
    Orbis non sufficit

    Trevor3007

  2. #2
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,880
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: colour row

    Hello, this does as you ask
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = Range("K2").Address Then
            If UCase(Target) = "YES" Then
                Range("A2:L2").Interior.ColorIndex = 33
            Else
                Range("A2:L2").Interior.ColorIndex = xlNone 
            End If
        End If
    End Sub
    Last edited by gallen; Aug 22nd, 2019 at 05:39 AM.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,451
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: colour row

    1. Select from A2 to L50
    2. Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =$L2="Yes" -> Format... -> Fill tab -> Choose your colour -> OK -> OK
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,451
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: colour row

    If you did want a vba solution, then I think gallen's Worksheet_Change code needs some tweaks to be robust and do what you want. To implement ..
    1. Right click the sheet name tab and choose "View Code".
    2. Copy and Paste the code below into the main right hand pane that opens at step 1.
    3. Close the Visual Basic window & test.
    4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Changed As Range, Cell As Range
      
      Set Changed = Intersect(Target, Range("L2:L50"))
      If Not Changed Is Nothing Then
        For Each Cell In Changed
          If UCase(Cell.Value) = "YES" Then
            Rows(Cell.Row).Resize(, Cell.Column).Interior.ColorIndex = 33
          Else
            Rows(Cell.Row).Resize(, Cell.Column).Interior.ColorIndex = xlNone
          End If
        Next Cell
      End If
    End Sub
    Last edited by Peter_SSs; Aug 22nd, 2019 at 05:53 AM. Reason: Typo in code
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

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
  •