Overrule conditional formatting

Beleriand_K

New Member
Joined
Jul 3, 2019
Messages
8
I've made a spreadsheet to keep score of a soccer tournament, and I would like to fill the cells if certain conditions are met. To do that I use Conditional Formatting.

Doing that I've encountered a very strange problem: Conditional Formatting overrules horisontal lines, but not vertical lines!?

I can't post a screen print here, so I'll try to do it manually with the current english Premier League as an example:

Liverpool 2 - 0 - 0 6 points | 1 - 0 - 0 | 1 - 0 - 0
Arsenal 2 - 0 - 0 6 points | 1 - 0 - 0 | 1 - 0 - 0

Manchester C 1 - 1 - 0 4 points | 0 - 1 - 0 | 1 - 0 - 0

I create a conditional formatting colouring the cells where the points are above 4. When I do that, the vertical lines seperating the points column from the home results and the home results from the away results is intact, but the horisontal line seperating Arsenal from Manchester C disappear. Apparently it is overruled by the conditional formating.

When I read the Excel help-manual it says that conditional formatting overrules manual formatting, but apparently that only goes for horisontal lines and not vertical lines. That doesn't make sense?
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,653
In the CF, as well as the fill, format the top and bottom borders.
In your example the border that remains must be the top border of the row below.
 

Beleriand_K

New Member
Joined
Jul 3, 2019
Messages
8
In the CF, as well as the fill, format the top and bottom borders.
In your example the border that remains must be the top border of the row below.
The second row (Arsenal) has a bottom border and the third row (Manchester C) has a top border. They both disappear when CF sets in. But the right and left borders in the two rows remain. That is what I don't understand.

Seen from CF's point of view, why is there a difference between a top border (disappears) and a right border (remains)? I would expect CF to either overrule both the borders or none of them. Not act differently depending on whether it is a top border or a right border.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,050
Office Version
365
Platform
Windows
Does the CF format you've used include top & bottom borders?
If I manually format borders & use CF to only change the background fill, then all borders remain.
 

Beleriand_K

New Member
Joined
Jul 3, 2019
Messages
8
Does the CF format you've used include top & bottom borders?
If I manually format borders & use CF to only change the background fill, then all borders remain.
Very good question. The left/right border in CF's border setting was grey, while the top/bottom border was blank. Then I Cleared the border setting, and all four borders became grey. And now CF let both the top/bottom and left/right border remain.

That was exactly what I was trying to do, so you've solved my problem. Thank you, very much :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,050
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,081,676
Messages
5,360,441
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top