Macro needed to replace conditional formatting

[gimley]

Hi everyone!

I would like to use a macro instead of conditional formatting t
highlight certain rows in my spreadsheet. I wonder if this i
possible.

My conditions to highlight the (entire) rows are as follow:

1. ($I2>2 OR $J2>2) -- highlight YELLOW

2. ($T2>1 AND ($D2>=20000 AND $D2<=39999)) -- highlight ORANGE

3. ($T2>5 AND ($D2>=60000 AND $D2<=69999)) -- highlight ORANGE

* I need to highlight from row 2 onwards to around row 1000+. As I nee
to add rows constantly, I do not know for sure how many rows I woul
have...

Please help me and let me know if it is possible to use a macro to ru
this. Thanks in advance

 

[David McRitchie]

You have only two colors therefore only two conditions and can
use or continue to use Conditional Formatting.
http://www.mvps.org/dmcritchie/excel/condfmt.htm
if you can't use Conditional Formatting you would have to use
and Event Macro if you want the coloration to be immediate.
http://www.mvps.org/dmcritchie/excel/event.htm
You would use .entirerow to effect the change to
the entire row.

Conditional Formatting would be preferable because it does not
make any difference how the data changes whether manually entered
or by calculation. If you have to use an Event macro they are best
used for comparisons to a manually changed cell. The C.F. is
applied to all cells that are selected, but since this is builtin to
Excel it should be very efficient compared to you trying to use
a macro.

Select cell J2 or any cell on the second row
Then select all cells Ctrl+A will leave the J2 as the active cell.
C.F. formulas are based on the active cell so it is
important that J2 (or any cell on row 2 in your case) be the active cell.
You could have A1 as the active cell if you changed the row of the formulas to row 1.
The reason for any cell on second row is that every cell on row 2
has exactly the same formula because of the $ in front of the column name.

Conditional Formatting: (after 1st hit, others conditions are ignored)
condition 1: =OR($I2>2,$J2>2) -- yellow
condition 2: =OR(AND($T2>1,$D2>=20000,$D2<=39999),
AND($T2>5,$D2>=60000, $D2<=69999))

1. ($I2>2 OR $J2>2) -- highlight YELLOW
2. ($T2>1 AND ($D2>=20000 AND $D2<=39999)) -- highlight ORANGE
3. ($T2>5 AND ($D2>=60000 AND $D2<=69999)) -- highlight ORANGE

Since I don't know if the cells being checked have constants
or have formulas I won't suggest a macro at this time. But you
would have to indicate that for an Event macro. Or run a
macro yourself just before you want to see the changes.

 

[David McRitchie]

Hi Gimley,
Though I think you would be best off with Conditional Formatting
here is an Event macro, install by right click on worksheet tab,
view code, and plop code in under Option Explicit
More information on Event macros in
http://www.mvps.org/dmcritchie/excel/event.htm#case

As mentioned in the previous reply this Event macro is based
on cells being manually changed as opposed to changes due to
formulas.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 9 And Target.Column <> 10 _
And Target.Column <> 4 And Target.Column <> 20 _
Then Exit Sub '-- checked column I, D, and T --.
If Target.Row = 1 Then Exit Sub
If Cells(Target.Row, 9) > 2 Or Cells(Target.Row, 10) > 2 Then
Target.EntireRow.Interior.ColorIndex = 36 'yellow pastel
ElseIf (Cells(Target.Row, 20) > 1 And Cells(Target.Row, 4) _

= 20000 And Cells(Target.Row, 4) <= 39999) _

Or (Cells(Target.Row, 20).Value > 5 And Cells(Target.Row, 4).Value _

= 60000 And Cells(Target.Row, 4) <= 69999) Then

Target.EntireRow.Interior.ColorIndex = 40 'orange pastel
Else
Target.EntireRow.Interior.ColorIndex = xlAutomatic
End If
End Sub

Tested with
D4: 20000 I4: 2 J4: 3 T4: 2 --yellowl
D5: 20000, T5: 2 -- orange

based on Conditional Formatting of
Conditional Formatting: (after 1st hit, others conditions are ignored)
condition 1: =OR($I2>2,$J2>2) -- yellow
condition 2: =OR(AND($T2>1,$D2>=20000,$D2<=39999),
AND($T2>5,$D2>=60000, $D2<=69999))
which was based on your original

1. ($I2>2 OR $J2>2) -- highlight YELLOW
2. ($T2>1 AND ($D2>=20000 AND $D2<=39999)) -- highlight ORANGE
3. ($T2>5 AND ($D2>=60000 AND $D2<=69999)) -- highlight ORANGE

You can see Colorindex values at
Color Palette and the 56 Excel ColorIndex Colors
http://www.mvps.org/dmcritchie/excel/colors.htm


[see original -- important lines are shown above in reply]

 

[gimley]

Thanks David!

Have tried your proposed conditional formatting method and it work
great! Will get down to the event macro part but I'm really new to al
these and I'll just bump around to see if it works.

I'll just let you know more about why I think I have to use a macr
(not too sure if it is better than C.F. in my case):

Everyday, I have to retrieve a spreadsheet from my company's database
In this spreadsheet, there are around 120+ columns and more than 3600
rows. This spreadsheet is updated concurrently by many differen
departments everyday and when I retrieve it, I need to see only what m
department does. So I will run a macro to delete unwanted columns an
rows (which I already have) and hightlight certain rows that nee
attention (which is what I'm trying to do here). I will have to do thi
everyday.

What is your suggestion?

Thanks

 

[David McRitchie]

With C.F. or with an Event macro the nice thing is that you
make a change and the C.F. or Event macro redoes the
coloring for the affected data. But from the sounds of it
you get the data and do not modify it except to eliminate
rows. If that is the case then a regular macro encompassing
both your deletions and the colorizations would be better.

Start with new test sheet, since C.F. would override normal
cell formatting, and this is doing normal cell formatting.

Sub Dept33()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, lastrow As Long, r As Long
'Create a copy of this sheet and make changes to the copy
'Copy the active worksheet to just before the currently active sheet [copy sheet]
ActiveSheet.Copy Before:=ActiveSheet
r = ActiveSheet.UsedRange.Rows.Count 'fix lastrow before starting
'Find last row with content in Column A
lastrow = Cells.SpecialCells(xlLastCell).Row
For r = lastrow To 2 Step -1
'-- Delete row if Department in Column A is not 33 --
If Cells(r, 1) <> "33" Then Rows(r).Delete
If Cells(r, 9) > 2 Or Cells(r, 10) > 2 Then
Rows(r).Interior.ColorIndex = 36 'yellow pastel
ElseIf (Cells(r, 20) > 1 And Cells(r, 4) _

= 20000 And Cells(r, 4) <= 39999) _

Or (Cells(r, 20).Value > 5 And Cells(r, 4).Value _

= 60000 And Cells(r, 4) <= 69999) Then

Rows(r).Interior.ColorIndex = 40 'orange pastel
Else
Rows(r).Interior.ColorIndex = xlAutomatic
End If
Next r
r = ActiveSheet.UsedRange.Rows.Count 'attempt fix lastcell aft deletes
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[gimley]

Thanks David! Your code works great and it is just what I needed.

However, I have 2 small problems here:

1. I have to remove the line If Cells(r, 1) <> "33" The
Rows(r).Delete to prevent the code from deleting all my rows. Is i
correct to do so?

2. After the highlighting is done, the borders I have for each cell i
gone. I'm using the light grey color as cell borders. I have even trie
to manually select the borders but it still won't work.

Is there a way to fix this?

Thanks for your help

 

[David McRitchie]

Hi gimley,
You indicated you only wanted the rows that referred to your dept.
so I put in an example to keep only a particular Dept (those
that have "33" in column A (column 1). Guess it's the good thing
changes were done to a copy of the worksheet if you weren't expecting that.

What did you have in mind when you indicated you wanted only your own department.

Interior color, and border color both override gridlines. You will
need to add borders. The gridlines are thin pale gray so you might
think they were gray borders. Ctrl+A, Format, cells, borders (tab),
optionally select a color, choose inside and outside borders. I thought
it would just generate a single line of code but that is not true.

You can add this line of code in your subroutine.
Call AllBorders

and add the following into a standard module:

Option Explicit
Sub AllBorders(Optional RngParm As Range)
Dim RNG As Range
Dim ScrUpdate As Boolean
ScrUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
If RngParm Is Nothing Then
Set RNG = Cells
Else
Set RNG = RngParm
End If
' On Error Resume Next
RNG.Borders(xlDiagonalDown).LineStyle = xlNone
RNG.Borders(xlDiagonalUp).LineStyle = xlNone
With RNG.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With RNG.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With RNG.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With RNG.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
On Error Resume Next
With RNG.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With RNG.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Application.ScreenUpdating = ScrUpdate
End Sub

Sub AllBorders_test()
' Call AllBorders Application.Range("D"))
Call AllBorders
' Call AllBorders(Cells) 'same as default
' Call AllBorders(Selection)
End Sub

 

[gimley]

Hi David,

You indicated you only wanted the rows that referred to your dept.

so I put in an example to keep only a particular Dept (those
that have "33" in column A (column 1). Guess it's the good thing
changes were done to a copy of the worksheet if you weren't expectin
that.

What did you have in mind when you indicated you wanted only your ow
department.

I have another macro that deals with the deleting or rows and columns
It will return me all the rows that my department needs.

And yes! Your AllBorders routine works great and I think I hav
completed my small project, all thanks to your help!

I will try to work around the code to see if C.F. is actually what
would prefer (because I might need to change the values occasionally)
If so then I will have to use the event macro that you suggeste
earlier.

Regards,
Gimle