Conditional Formatting

Ahlis1979

New Member
Joined
May 22, 2017
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi!

If I have a cell with a address: D12 is showing $C$12

Can I use the value in D12 to do a formatting with the Conditional Formatting so it make a fill color of the cell C12?

Best regards
Ahlis
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,022
Office Version
  1. 365
Platform
  1. Windows
You can use this Conditional Formatting formula:
Excel Formula:
=ADDRESS(ROW(),COLUMN())=$D$12
Just select the whole range that you want to apply this rule to (any possible cell you may enter in cell D12), go to Conditional Formatting, choose the formula option (last one), enter the formula, and select your highlighting color.
 

Ahlis1979

New Member
Joined
May 22, 2017
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hope you understand my question better now.

Daglig Styrning.xlsx
PQRSTUVWXYZAAABACADAE
2IdagROW9
323CELL27
4AddressAA$9Cell that should be formatted this day with a colored frame
50000000030
60000000000
700000000000000
800000000000300
900900002040000
100000000000
1100000000
12
130000000
140000000
150000000
160000000
1700190000
180000000
190000000
Settings
Cell Formulas
RangeFormula
P3P3=DAY(TODAY())
R2R2=MAX(Q5:W11)
R3R3=MAX(Q13:W19)+8
R4R4=ADDRESS(Settings!R2,Settings!R3,2)
Q5:W11Q5=IF(I5=$P$3,ROW(),0)
AA5AA5=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],1))
AB5AB5=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],2))
AC5AC5=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],3))
AA6AA6=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],4))
AB6AB6=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],5))
AC6AC6=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],6))
Y7Y7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],7))
Z7Z7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],8))
AA7AA7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],9))
AB7AB7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],10))
AC7AC7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],11))
AD7AD7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],12))
AE7AE7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],13))
Y8Y8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],14))
Z8Z8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],15))
AA8AA8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],16))
AB8AB8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],17))
AC8AC8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],18))
AD8AD8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],19))
AE8AE8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],20))
Y9Y9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],21))
Z9Z9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],22))
AA9AA9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],23))
AB9AB9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],24))
AC9AC9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],25))
AD9AD9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],26))
AE9AE9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],27))
AA10AA10=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],28))
AB10AB10=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],29))
AC10AC10=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],30))
AA11AA11=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],31))
Q13:W19Q13=IF(I5=$P$3,COLUMN(),0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AA5:AC11,AD7:AE9,Y7:Z9Cell Value=0textNO
AA5:AC11,AD7:AE9,Y7:Z9Cell Value=1textNO
AA5:AC11,AD7:AE9,Y7:Z9Cell Value=4textNO
AA5:AC11,AD7:AE9,Y7:Z9Cell Value=3textNO
AA5:AC11,AD7:AE9,Y7:Z9Cell Value=2textNO
Y5:AE11Expression=ADDRESS(ROW();COLUMN())=#REF!textNO


or do I have to use a picture that moves every day to do this kind of function?

I don't want to use VBA for this because I have to use it in Excel Online when I'm done with the file.

Best Regards
Ahlis
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,022
Office Version
  1. 365
Platform
  1. Windows
Your pictures do not seem to line up with your original question.

Can you make sure everything lines up, is consistent, and clearly walk us through an actual example?
 

Ahlis1979

New Member
Joined
May 22, 2017
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

This is from the orginal sheet:

The calendar is made like a cross. If something happened I write down the date and what happened. Then it show a color in the cross based on type of problem it was that day.

I use an image as a overlay above the cross so I could see the days.

A neat function would be if I also can highlight the current day with colored frame.

For the moment the images black lines is above excels lines so I have to remove the lines from the picture if there is a formula that can handle this. Otherwise I guess I need to use another picture above the other picture that moves every day. Maybe vba is needed for this kind of function?

Daglig Styrning.xlsx
BCDEFGH
5030
6000
70000000
80000300
92040000
10000
110
Översikt
Cell Formulas
RangeFormula
D5D5=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],1))
E5E5=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],2))
F5F5=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],3))
D6D6=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],4))
E6E6=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],5))
F6F6=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],6))
B7B7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],7))
C7C7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],8))
D7D7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],9))
E7E7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],10))
F7F7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],11))
G7G7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],12))
H7H7=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],13))
B8B8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],14))
C8C8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],15))
D8D8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],16))
E8E8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],17))
F8F8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],18))
G8G8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],19))
H8H8=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],20))
B9B9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],21))
C9C9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],22))
D9D9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],23))
E9E9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],24))
F9F9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],25))
G9G9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],26))
H9H9=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],27))
D10D10=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],28))
E10E10=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],29))
F10F10=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],30))
D11D11=MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],31))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:H11Expression=ADDRESS(ROW();COLUMN())=Settings!$R$4textNO
D5:F11,G7:H9,B7:C9Cell Value=0textNO
D5:F11,G7:H9,B7:C9Cell Value=1textNO
D5:F11,G7:H9,B7:C9Cell Value=4textNO
D5:F11,G7:H9,B7:C9Cell Value=3textNO
D5:F11,G7:H9,B7:C9Cell Value=2textNO


Daglig Styrning.xlsx
IJKLMNOPQRSTUVW
2IdagROW9
323CELL4
4AddressD$9
51230000000
64560000000
7789101112130000000
8141516171819200000000
9212223242526270090000
102829300000000
11310000000
12
130000000
140000000
150000000
160000000
1700190000
180000000
190000000
Settings
Cell Formulas
RangeFormula
P3P3=DAY(TODAY())
R2R2=MAX(Q5:W11)
R3R3=MAX(Q13:W19)-15
R4R4=ADDRESS(Settings!R2,Settings!R3,2)
Q5:W11Q5=IF(I5=$P$3,ROW(),0)
Q13:W19Q13=IF(I5=$P$3,COLUMN(),0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I5:O11Expression=$P$3textNO


Best Regards
Ahlis
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,022
Office Version
  1. 365
Platform
  1. Windows
As I asked for in my previous post, please walk us through an actual example, step-by-step, based on your images.
That may help clarify what you are trying to do, as it is still not clear to me.

In walking us through an actual example, you will need to tell us:
- Exactly what cell you are updating (tell us the cell address)
- Exactly what you are entering into that cell
- What the expected outcome of that action is (what happens to other cells, providing the details of the cell addresses and exactly what happens to them)
 

Ahlis1979

New Member
Joined
May 22, 2017
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Have problems with the XL2BB so I only post a image today.

The only question I have is if the Conditional Formatting could understand if it looks in B23 and use that address in that cell and then color the border of that cell (G8).

B23 = $G$8 = Colored border
all other cells in this range D4:J10 should have no border

Tomorrow
B23 =$H$8 = Colored border
all other cells in this range D4:J10 should have no border

and so on...
 

Attachments

  • cross.PNG
    cross.PNG
    80.9 KB · Views: 2

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,022
Office Version
  1. 365
Platform
  1. Windows
The only question I have is if the Conditional Formatting could understand if it looks in B23 and use that address in that cell and then color the border of that cell (G8).
I showed you how to do that in my very first reply. Did that not work for you?

Here are step-by-step instructions on how to do that.
1. Select cells D4:J10
2. Go to Conditional Formatting, and select New Rule then Use a formula to determine which cells to format
3. Enter in the following formula:
Excel Formula:
=ADDRESS(ROW(),COLUMN())=$B$23
4. Click on the Format button
5. Go to the Border tab and select the Outline option
6. Click OK twice.

This will put the highlight around cell G8.
If you cnange the address in cell B23 to any other cell in range D4:J10, it will move the outline to that cell.
 
Solution

Ahlis1979

New Member
Joined
May 22, 2017
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Now it worked.

First time I didn't get it to work. Strange. Thanks for the help. :)

Best regards
Ahlis
 

Watch MrExcel Video

Forum statistics

Threads
1,130,155
Messages
5,640,448
Members
417,143
Latest member
boukadidanizar

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