COLORIZE ALL ROW in sheet

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

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;-)

Reply | Quote

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.

Reply | Quote

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.

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

Reply | Quote

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.

Reply | Quote

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.

As usual Kiss

Reply | Quote