Trying to compare 2 cells B1 and B5 and apply style to B5, then copy condition to other cells without the range changing.
Setting conditional format for cell B5
Condition 1 - cell value is greater than B1 apply style ‘good’ to B5
Condition 2 - cell value is less than B1 apply style ‘bad’ to B5
Range - B5
Now want to copy this condition to each cell in row starting with B6
Problem is - range does not stay relative and expands to more cells
When copying format of B5 to B6 the conditional format range should be B6
But it is changed to B5:B6
Hallo
Change in your Conditions B1 to $B$1
ps. on Conditions with CellValue it doesnt matter, if they applied to single Cells or CellRange(s)
Sorry, my mistake - got my columns/rows mixed up:
Compare E1 value to A1 value and apply style to E1.
Then copy format of E1 to remaining columns.
So conditional format in F1 compares F1 value to B1 value and applies style to F1, and so on,
(always comparing current cell value to cell values 4 prior columns back)
But conditional format range for F1 becomes E1:F1 where I expected it to be F1 only.
As I expected relative range of E1 in cell E1 when copied, range in F1 to be F1.
(Each column is a range of dates dividing a month into 4 parts)
(days ‘1-7’ days ‘8-14’ days ‘15-21’ days ‘22-endofmonth’)
(so cell E1 in column ‘1-7 Feb’ is being compared to cell A1 in column ‘1-7 Jan’)
Select E1:Z1 and:
- Condition:
CellValue | greater than | A1
Style: →good
- Condition:
CellValue | less than | A1
Style: →bad
Assuming by Select you meant to say make Range E1:Z1
This does not work because each column compares to A1.
I.e. compared cell to current cell is not getting relatively incremented.
F1 shows comparisons are to A1 and should be B1.
Thanks, got it - an example is worth a thousand messages!
I selected F1 and looked at the conditional format and the conditions compare to A1 not B1.
It is not intuitive - why doesn’t it show B1? Same for every cell thru Q1, always A1 ???
It appears to be a matter of semantics - the E1 conditions are a template applied to all cells in the range.
Thus, the A1 is not incremented in the successive cells when you look at its conditional format.
Documentation is not clear on this to explain that the relative incrementing is really taking place!
Do I have to do this for each row I want to apply? (all rows do not get the conditional formatting)
On your sheet, in A3 I entered “=A1+1”, then copied thru to Q3
Then I format painted E1 to E3 - the conditional format range changed to E1:Q1,E3
I changed to E3:Q3 and the conditional formatting in row 1 disappeared - strange!
I undid the change and the copy.
I then entered the conditional formatting of E3 from scratch and everything looks good.
So, it appears I must do the following:
For cell Ex (each row) add the conditional formatting from scratch.
When I extend the number of cols, edit conditional formatting of each Ex cell to extend the range.
Correct?
It would be more sensible to edit E1 only as to range and copy its format to all appropriate rows.
Thus, the relative rows would change appropriately in the conditional formats.
Agree?