Macro/Conditional formatting? Which to use and how?

[Robertislearning]

Hello everyone!

I run several large reports daily that looks similar to this:

(this is a fictional report)
Number First Last Position Years in league
88 Michael Irving WR 5
84 Jay Novachek TE 10
8 Troy Aikman QB 8
22 Emmitt Smith RB 12

What I would like to do is have either a Macro or Conditional formatting to go through the entire report and highlight every line that has a player who has more than 9 years in the league. For example, I'm trying to figure out how to get the 2nd and 4th lines to be filled in with yellow shading automatically without having to manually go through the entire report. This will not only save time, but I will be able to produce much more accurate reports.

Any assistance on this is greatly appreciated!

Thanks,

Robert.

 

[DaleWatson123321]

Robert,

For Conditional Formatting, here are the Steps...

1) In Cell A2, use from the menu: Format - Conditional Formatting

2) Change "Cell value is" to "Formula is"

3) Entering the follow formula: =$E2>=9

(I gather than where you say more than 9 years, you actually mean 9 or more years - remove the "=" if required)

4) Click "Format" and choose Pattern - then pick a color (Yellow)

5) OK to close out of Conditional Formatting.

6) Copy the cell you just formatted (A2)

7) Highlight the entire range where you want this formatting used.

8) So you don't overwrite any existing data, use: Edit - Paste Special - Formats.

Hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[PeterMoran]

Hi Robert,

Dale has answered your first request as well as anyone could, so I will just add a suggestion for your mention of shading alternate rows.

To do this requires a second conditional format with the following formula:

=MOD(ROW(),2)=1

just as Dale has mentioned. However if you need them both together then you will see an Add button on the Conditional Format window (you can have up to three conditional formats). If you select the Add button you can add the second format, otherwise follow Dale's procedure exactly.

Good Luck!

Peter Moran
Two heads are always better than one!

 

[Robertislearning]

Dale and Peter,

Thank you very much for your quick responses. I have not had the time to apply your advise but it looks like it'll do the job.

Again, thanks for your input!

Robert

 

[Robertislearning]

I've made progress! I was able to configure conditional formatting to get a range of cells highlighted. I'd like to see if I can take it a step further. In the same example, how do I get an entire row to be highlighted? I would like to learn how to have conditional formatting or a Macro go through an entire 400+ line report similar to this one and highlight every line that has a value of 'Jay' in column B and a value greater than 9 in column E. Then go through the entire report again checking for a value of 'Michael' in column B with a value greater than 3 in column E. If these two values are met, then I'd like to have the entire row highlighted.



Number First Last Position Years in league
88 Michael Irving WR 5
84 Jay Novachek TE 10
8 Troy Aikman QB 8
22 Emmitt Smith RB 12


Thanks in advance for your help. I hope I was able to explain this clearly.

Robert.

 

[PeterMoran]

Hi Robert,

I used your data and set up Conditional Formats as follows:

1. Select the Range of cells to be included (A2:E5) - you might like to make a range name (select area then Insert Name Define) for this - it will then be a cinch to select (Just click on the name box and select the name).

2. Select Format, Conditional Formatting, then replace "Cell Value is" with "Formula is" and enter the following formula:

=AND($B2="Jay",$E2>9)

3. Select how you want to highlight the rows.

Jay's row is highlighted in bright green as I wanted.

Explanation:

AND - all conditions have to be true, you could use "OR" if you only needed either condition to be true.

"$B2="Jay"" - $B is an absolute column in the cell address which will not change, while the 2 will change with every row. Try it and see the result.

Likewise "$E2>9" On all rows it will check if Col E value > 9.

Have a play with this and post again if you need any further help.

Good Luck!

Peter Moran
Two heads are always better than one!

 

[Bowers74]

Enter the following code into a standard VBA module:

Sub Highlight_9_Plus()
Do Until IsEmpty(ActiveCell)
If ActiveCell.Offset(0, 4).Value > 9 Then
ActiveCell.EntireRow.Interior.Color = vbRed
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

Please note that this code will only work with the example that you gave. You will need to modify it so that it works with your REAL spreadsheet.

Be sure that the column you start in in filled completely to the bottom of your table. and set the first Offset to the correct number of columns.