Conditional formatting slows down spreadsheet

[0nyx175]

Hi guys.

I have a spreadsheet which shows information about internal complaints against people in our business from other areas of the business which has the following columns

Date
Staff member 1st name
Staff member surname
Staff number
Job
Department
Based
Company
Complainant
Complainant Surname
Complainant Staff number
Complainant Job role
Complainant based

I need to be able to easily identify those who come up more than once (staff # complained about, area of the business complained about, staff # making the complaint, area of the business complaining) so that we can highlight repeat offenders. Firstly I set up conditional formatting but that only let me have three pieces of criteria so I then set up four columns:

Staff # complained about
Area complained about
Staff # complaining
Area complaining

I used conditional formatting in each cell with this code

=COUNTIF($I:$I,I2)>1

Obviously changing it depending on which area im looking at.

The problem with this is that it is really really slowing down the spreadsheet. Can anyone suggest a better way to do this?

 

[Jerry Sullivan]

Jerry

I was just going to have another look at the link but I'm getting the same error message again.

That's strange it works for me.
Here is the text to copy paste into a browser.
http://dmcritchie.mvps.org/excel/slowresp.htm#likeformats

Perhaps it will work for you without the #likeformats reference
http://dmcritchie.mvps.org/excel/slowresp.htm

 

[Norie]

Jerry

I don't know what the problem was but the link now works.

Still not sure what the recommendation is, format an entire row even if the majority of the cells in the column are empty?

That doesn't sound right.

Perhaps it's more to do with the formula used in the conditional formatting.