conditional formating

sony

Board Regular
Joined
Jun 15, 2002
Messages
126
dear excel expert,

I want to the cell in yellow under the conditions that the horizontal day (e.g. Fri) match the vertical day (e.g. Fri).

HOW can I do that ?
Book3
ABCDEFGHI
19/29/39/49/59/69/79/8
2ETDPODMonTueWedThuFriSatSun
3FriNLRTM
4FriGBSOU
5TueGBSOU
6MonNLRTM
7MonGBSOU
8SatESBCN
9SatESBCN
10MonNLRTM
11MonGBSOU
12ThuNLRTM
13ThuGBSOU
14SatNLRTM
15SatGBSOU
16WedNLRTM
17WedITGOA
Sheet1
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
sony,

try:

=OFFSET(c3,0,(COLUMN()-1)*-1,1,1)=OFFSET(c3,(ROW()-2)*-1,0,1,1)

as the conditional format for c3 & copy the format across / down as necessary. Gave me the following result:
Book10
ABCDEF
1
2MonTueWedThurFri
3Mon
4Mon
5Tue
6Tue
7Wed
8Wed
9Thur
10Thur
11Fri
12Fri
13
14
Sheet1


Paddy
 

sony

Board Regular
Joined
Jun 15, 2002
Messages
126
dear paddy,
tks yr quick help. actually i am novice about this.
I do not quite understand how & where to put your formula, could u be kind enough to explain step by step?

tks again
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
On 2002-09-01 20:11, sony wrote:
dear paddy,
tks yr quick help. actually i am novice about this.
I do not quite understand how & where to put your formula, could u be kind enough to explain step by step?

tks again

OK!

Lets take the area of data you posted in the example - you're interested in c3:i17

1) select cell c3
2) holding the left mouse button down, select the rest of the area you want to format
3) go to format | conditional formatting
4) change condition 1 from 'Cell value is' to 'Formula is'
5) paste in the formula I posted above
6) click the format... button (below the formula bar in the conditional format popup window)
7) select the patterns tab & pick a colour...
8 ) Click OK twice

Should do it - post back with your results,

Paddy

EDIT: to change 'right', which was wrong, to 'left', which is right, in point (2) - Ooops!
This message was edited by PaddyD on 2002-09-01 20:27
 

sony

Board Regular
Joined
Jun 15, 2002
Messages
126

ADVERTISEMENT

Hi Paddy,

Tks. I can do it in my origina sheet.

But now i have to apply the formula to another sheet where the cell position is changed, so i suppose i change c3 to be g3 but it looks not working, why?
Book6
ABCDEFGHIJKL
1Sony9/29/39/49/59/69/7
2CarrierFeuSvcCLSETDPODMonTueWedThuFriSat
3APLAPL7CEXSunMonNLRTM
4FIHEL
5JEXMonTueNLRTM
6FIHEL
7FRLEH
8HapadHAP35LpDTueWedGBSOU
9ESBCN
10FIHEL
11FRLEH
12HYNCEX10CEXSatMonDEHAM
13DKCPH
14GBSOU
15JEX15JEXMonTueDEHAM
16DKCPH
17NLRTM
18GBSOU
19KLINEKLI20ECSThuFriFRLEH
20PDMSunTueNLRTM
Sheet2
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
which column are you matching on? the day names in col D or E?

Paddy
 

sony

Board Regular
Joined
Jun 15, 2002
Messages
126

ADVERTISEMENT

Hi Paddy,

I want to match the day of the column E.
How would be the difference in the formula when i change the column i refer to?
I am curious to learn from you.
Man thanks to Paddy
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
with the data as you posted, follow the procedure set out above, but use this formula:

=OFFSET(G3,0,(COLUMN()-COLUMN($E:$E))*-1,1,1)=OFFSET(G3,(ROW()-ROW($2:$2))*-1,0,1,1)


offset has the syntax:

OFFSET(reference,rows, cols,height,width)

so what we're doing is seeing if one offset value = another offset value. Lets take the first one:


=OFFSET(G3,0,(COLUMN()-COLUMN($E:$E))*-1,1,1)

offset(G3...

the G3 is the start cell. Cos we're entering this as a relative refernece (rather than, say, $e$3, it will update appropriately for all the cells we put the formula in.

=OFFSET(G3,0...

this says offset by no rows - so this one is getting the day value from the column. Question is, which one?

=OFFSET(G3,0,(COLUMN()-COLUMN($E:$E))*-1...

column() returns the column number of the cell. For cell g3 this is 7. The column we want to offset to is col e. the column number for col e is 5. So we want to offset by 2 columns. COLUMN()-COLUMN($E:$E) - the number returned by column() will change for each different column the formula is in; the number returned by COLUMN($E:$E) will always be 5 (cos it's fixed wqith an absolute reference) so we'll always know how many cols we are away from col e. The *-1 bit is 'cos positive column offsets go to the right - we need a negative one to go to the left.

=OFFSET(G3,0,(COLUMN()-COLUMN($E:$E))*-1,1,1)

the last 2 values tell excel to return the data from one cell only.


The second offset is essentially the same, but for rows:

=OFFSET(G3,(ROW()-ROW($2:$2))*-1,0,1,1

where the ROW()-ROW($2:$2))*-1 works out how many rows to offset...


If you need, change the column($e:$e) and row($2:$2) bits in the formula to whichever column / row the day names are in & it should always work...


Paddy
 

sony

Board Regular
Joined
Jun 15, 2002
Messages
126
Hi Paddy,
It's marvellous! I can do it now by myslef in the new sheet.

You are a very good instructor.
Thank you so much for your clear explanation.
 

Forum statistics

Threads
1,143,613
Messages
5,719,716
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top