Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Have a sheet filled with value from A2 to V3000

    In column T have a number value.

    I want to colorize all row based:

    if the number in T is in the 0 to 45 no action

    if the number in T is in the range 46 to 90 colorize entirer rows set from A to T in red

    if the number in T is in the range 91 to 120 colorize entirer rows set from A to T in green

    if the number in T is in the range 121 to 9999 colorize entirer rows set from A to T in blue

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Select columns A:T. Cell A1 should be the active cell within the selection.
    Select Format | Conditional Formatting...

    Select Formula Is from the first dropdown.
    In the box next to it, enter the formula =$T1>120
    Click Format...
    Specify the Font or Pattern color blue.
    Click OK.
    Click Add >>

    Select Formula Is from the first dropdown.
    In the box next to it, enter the formula =$T1>90
    Click Format...
    Specify the Font or Pattern color green.
    Click OK.
    Click Add >>

    Select Formula Is from the first dropdown.
    In the box next to it, enter the formula =$T1>45
    Click Format...
    Specify the Font or Pattern color red.
    Click OK.
    Click OK to close the Conditional Formatting dialog.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='798334' date='16-Oct-2009 12:55']Select columns A:T. Cell A1 should be the active cell within the selection.
    Select Format | Conditional Formatting...

    Select Formula Is from the first dropdown.
    In the box next to it, enter the formula =$T1>120
    Click Format...
    Specify the Font or Pattern color blue.
    Click OK.
    Click Add >>

    Select Formula Is from the first dropdown.
    In the box next to it, enter the formula =$T1>90
    Click Format...
    Specify the Font or Pattern color green.
    Click OK.
    Click Add >>

    Select Formula Is from the first dropdown.
    In the box next to it, enter the formula =$T1>45
    Click Format...
    Specify the Font or Pattern color red.
    Click OK.
    Click OK to close the Conditional Formatting dialog.[/quote]

    TKS Hans i know this way but with a macro code ....because i filter data from a master sheet and recopy in a template sheet... and after the copy i want to colorize the rows;-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is a macro:

    Code:
    Sub ColorRows()
      Dim r As Long
      Dim m As Long
      m = Cells.Find(What:="*", SearchOrder:=xlByRows, _
    	SearchDirection:=xlPrevious).Row
      For r = 2 To m
    	Select Case Range("T" & r).Value
    	  Case Is > 120
    		Range("A" & r & ":T" & r).Interior.Color = vbBlue
    	  Case Is > 90
    		Range("A" & r & ":T" & r).Interior.Color = vbGreen
    	  Case Is > 45
    		Range("A" & r & ":T" & r).Interior.Color = vbRed
    	End Select
      Next r
    End Sub
    If you want to change the text color instead of the background color, change Interior to Font.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='798337' date='16-Oct-2009 13:05']Here is a macro:

    Code:
    Sub ColorRows()
      Dim r As Long
      Dim m As Long
      m = Cells.Find(What:="*", SearchOrder:=xlByRows, _
    	SearchDirection:=xlPrevious).Row
      For r = 2 To m
    	Select Case Range("T" & r).Value
    	  Case Is > 120
    		Range("A" & r & ":T" & r).Interior.Color = vbBlue
    	  Case Is > 90
    		Range("A" & r & ":T" & r).Interior.Color = vbGreen
    	  Case Is > 45
    		Range("A" & r & ":T" & r).Interior.Color = vbRed
    	End Select
      Next r
    End Sub
    If you want to change the text color instead of the background color, change Interior to Font.[/quote]

    WOW brillinat solution...

    but before to copy new row in template i want to delete the old rows... i use:

    WS_DEST.Range("A2:V5000").Clear

    but the code clear also the font size!!!! i want maintain the size of fonts Arial 8

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

    Code:
    With WS_DEST.Range("A2:V5000")
      ' Clear the values
      .ClearContents
      ' Remove coloring
      .Interior.ColorIndex = xlColorIndexAutomatic
    End With
    Again, if you want to color the text instead of the background, use Font instead of Interior.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='798347' date='16-Oct-2009 13:33']You could use

    Code:
    With WS_DEST.Range("A2:V5000")
      ' Clear the values
      .ClearContents
      ' Remove coloring
      .Interior.ColorIndex = xlColorIndexAutomatic
    End With
    Again, if you want to color the text instead of the background, use Font instead of Interior.[/quote]

    As usual Kiss

Posting Permissions

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