excel conditional formatting 1

[Cpreston]

Hi

I have made a conditional format that highlight cells in column K to red when older than so many days, this works ok.

Is it possible to make the cells in column B to also go red if the adjacent cells in K are red.

So where K2 is red then I would like B2 to also be red (although it holds different data

Any ideas please.

Thanks

 

[SkipVought]

Hi,

Yes IF you use a formula in you column b selection CF

=$K2>SoManyDays

 

[Cpreston]

Hi

I have =$K2>60 and this as highlighted every row in the range in red in the G column

The range is set to =$G$2:$G$20, I have tried Stop if True unticked and ticked.

Thanks

 

[SkipVought]

The data you've posted is not consistent or you have not explained fully what's going on.

First you stated that you have cells in column K to red when older than so many days.

Now you state that the CF range is in column G???

???

 

[Cpreston]

Sorry the dates are in column k and if the date is
More than 60 days older than todays date i would like the column g adjacent cells to turn red. Hope that is a better explanation sorry about that

 

[jonsi]

As it's a CF just use the same condition (but put the CF in G2)

 

[SkipVought]

=Today()-$K2>60

 

[Cpreston]

As I put earlier i did put the CF in G2 but it highlighted all the cells in G

=$k2>60 and the range set to G column
I have also tried =$k2=TODAY()-60 and this did not work at all

Thanks

 

[Cpreston]

Sorry just saw the formula you put I will give it a go and update you with result many thanks

 

[Cpreston]

Hi

I now have it so it works using =Today()-$K2>60

Last question I hope on this subject, how do I stop it so the rule only applies if the cell as something in it. If K is empty then it seems to fill in the G adjacent cells. I have set it as below and also tried Stop If true (both ticked and unticked) but even it there is only 100 rows in G it continues to 200 highlighted.

=$G$2:$G$200

Thanks

 

[Cpreston]

Sorry, I have had to rethink how this is working and now need it to look for the dates from another sheet

I now have this

="Data!=Today()-$K2>60" and the range as =$P$5:$P$200

This condition is set on the sheet where the P column is.

It is however not highlighting as expected on the sheet.
I did have errors regarding using - and to try and use ' in the formula. I tried several ways and this is the only one it would except ="Data!=Today()-$K2>60"
I have a feeling though this is incorrect hence it is not highlighting in the P column.

Hope you can help, thanks

 

[Cpreston]

Hi

I have had some success, I now it working using this formula in the condition. Probably not perfect but it works.

=DATA!$k2:$K150 < TODAY() -60

However, I save the spread sheet and then go back in and the CF is not there, I have applied it many times and saved, but on going back in the condition as gone again. Any Ideas please.

Thanks

 

[SkipVought]

So you have the active sheet and sheet, Data, correct?

So the range of the CF is $P$5:$P$200 on the active sheet and K2 is on what the Data sheet I'm guessing, as it is not at all clear.

=Today()-Data!$K2>60

Maybe???

 

[Cpreston]

Hi

Yes that worked thanks alot =Today()-Data!$K2>60

I have sent it to someone who needs to use it and we are now getting You cannot use references to other worksheets or workbooks for conditional formatting criteria. They have Excel 2007. I have tried to use a named range as a solution as suggested in some google links but cannot get it to work in their versions. Current code is =TODAY()- date >60
Date is the named range in the Data worksheet.

I will keep working on it but if any one knows of a simple solution that would be great.

Many thanks

 

[SkipVought]

Here's the problem with using a reference, named or not, on another sheet.

In your CF, each row refers to the same row on the other sheet.

Are these rows ABSOLUTELY IN SYNCH??? That's a problem!

 

[Cpreston]

It all worked fine in my Excel which is 2010 they have 2007 which should work still. I tried naming way as it was suggested on google but I am not sure the formula is correct. The sheet uses same SQL view so data appears to be ok.

Thanks

 

[Cpreston]

Hi

I now have this formula in the exel that as the issue with a named range

=TODAY()-(_10_02_2015)>60

Unfortunately it is highlighting all the entries in column P. I have tried to alter the formula but cannot get it to work, this is as close as I could get a response.

Any ideas on what is wrong with the formula please. The _10_02_2015 is the named range given by excel.

Many Thanks

 

[SkipVought]

What is the value or range of values returned by

=_10_02_2015

???

 

[Cpreston]

In the named range are date fields for example 20/04/2015

I will try the =_10_02_2015 in the morning and update

Thanks

 

[SkipVought]

So are these ranges on the other sheet in the same rows with same number of rows as the sheet containing your CF?