Cell reference in conditional formatting
felix fritz
My apologies if all the text in this document is in German, I'll try to explain it as well as possible.
Basically, this is just a sheet that contains the dates and times an employee was working. All the weekdays are automatically generated, e.g. when the month is set to October 2013, the weekdays automatically update. ("Di" -> "Tu"/tuesday, "Mi" -> "We"/wednesday, ...) 
This works out alright, so I don't have any problems with that. What the actual goal is, I wanted to make it a little bit better readable by adding thicker lines after every sunday, signalizing the end of the week.
Using the conditional formatting, I've managed to do that for every line. E.g. for line 14, I used the formula =MOD(VALUE($B$14);7)=1 to determine, if the value in column B and line 14 is in fact a sunday. If the modulus of the weekday divided by 7 is 1, then it is a sunday and the formatting style is applied to those cells.
Now, this formula makes it a little hard to work with. I'd basically have to copy-paste the lines each time and change the line number for each line. I thought it would be possible to make it more dynamic by leaving out the $-symbol (=MOD(VALUE($B1);7)=1), but for some reason it didn't work for me.
What do I have to type in inside VALUE to reference the B-Column in the same line?
Jerry
Okay, the issue was that you were applying your condition on a range on the wrong active cell.
An active cell is the only cell which is slightly less blue in the selected region; for instance, in the picture below, the active cell is A1:
And if this is the active cell, you will be able to use your formula:
=MOD(VALUE($B1);7)=1
There's a simpler formula though:
=WEEKDAY($B1)=1
This means that if the active cell was A32 or H32 in the picture, the formula wouldn't have worked, because the condition for formatting is being applied to the wrong cell!
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: PSPDFKit give error `documentRef is nil , cannot get pageRef`
- Next: HTTP Status Code 411 - Length Required
Related
Related Related
- 1
Cell reference in conditional formatting
- 2
Conditional formatting based on cell reference
- 3
Conditional formatting based on reference cell value for each cell in row
- 4
Excel cell reference to it´s previous value (conditional formatting)
- 5
Cell Formatting, Conditional Formatting, or Formula
- 6
Conditional Cell Formatting in KOGrid
- 7
Conditional Formatting on cell values
- 8
Conditional formatting cell error
- 9
VBA Conditional Formatting if Cell Not Between
- 10
VBA applying conditional formatting to cell
- 11
Conditional formatting upon another cell
- 12
VBA applying conditional formatting to cell
- 13
Excel IF statement conditional cell formatting
- 14
Conditional formatting based on cell value
- 15
Conditional formatting based on previous cell
- 16
Conditional formatting based on another cell's value
- 17
Multi-function conditional formatting cell range
- 18
Conditional Formatting with Custom Formula referencing the cell itself
- 19
(Excel) Conditional Formatting based on Adjacent Cell Value
- 20
Wrong cell when conditional formatting with values
- 21
Change the cell color using conditional formatting with Epplus
- 22
conditional formatting entire row if cell contains text
- 23
Excel conditional formatting only applies to first cell
- 24
Conditional Formatting in Excel with Formula Current Cell
- 25
Conditional formatting based on a date OR if another cell is blank
- 26
Conditional formatting rows based on cell dynamic
- 27
Color that cell that is listed w/ conditional Formatting
- 28
Excel conditional formatting based on cell content
- 29
Excel Conditional Formatting based on Previous Cell
Comments