Does VBA conditionnal formating suffer from the same bug as "manual" conditionnal formating ?

Baptiste 25

Hi,

We have an issue on a shared file. We use some conditionnal formating and some time it changes itself the range where it should apply.

For exemple if A1 = 100 B2 is in red, it will work perfectly but I don't why at a random time it will go if A1 = 100 C2 is in red. (it is more if A1="..." and B1="..." -> A1:K1 is in red and A1:K1 change for A1:H1;J1:K1)

So I saw that we can use macro to put conditionnal formating but I was wondering if the same "bug" will occur with the macro or if it safe ?

+Is it possible to convert my already used conditionnal formating into VBA one ? Or do you have exemple ?

Thanks for reading

Mikey Bronowski 151 Reputation points

2024-02-07T09:35:12.12+00:00

Hey, if I understand that correctly that's your scenario.

The conditional formatting (Note This Worksheet in the selection)

This is a simple example, and I don't know what are your $ positions in the formula.

Sometimes, when you copy one cell, the conditional formatting goes with it creating another rule. When you check the Conditional Formatting Rules Manager you will see multiple rules.

I know it's not an answer regarding VBA question, but let's start there and see if that helps.
Baptiste 25 Reputation points

2024-02-07T09:47:30.3366667+00:00

Hi thanks for the comment, This is quite right : I have this simple conditionnal formating (I have another table in the sheet with more conditionnal formating but the same issue occurs) :

And yesterday I saw 5 duplicated rules ... (I didn't screenshoted it). My coworker copied some lines I think so it might be why it "bugged". Thanks for your answer, is there any tips to stop this bug other than stop copy/paste ?
Mikey Bronowski 151 Reputation points

2024-02-07T10:09:40.5533333+00:00

One way would be to paste as a value or a formula (right-click and pick from "Paste Options", or CTRL+SHIFT+V), instead of simply pasting with CTRL+V.

If you want to use a keyboard only: Press ALT, then one by one E - S (will open Paste Special window) - V (for Values) or F (for Formulas) - ENTER
Baptiste 25 Reputation points

2024-02-07T10:18:04.4533333+00:00

Ok this will solve the issue when we copy/paste but I am pretty sure that it bugs at another time. Sometimes it bugs for this big boy :

I have a dublication of the formula but I have a weird applies to I have a superposition of range and/or the exclusion of a column ... I think it bugs with easier things than copy/paste so your tips will help us but not for all our case... The VBA things will help us but if it buggs it ends :( and i don't know how to use my =and($k12>$E12) apply to $K...
Mikey Bronowski 151 Reputation points

2024-02-07T10:30:00.11+00:00

Regarding your last part, do you want to apply one rule to the whole K column, based on the values in corresponding rows?

If so, I would try this

If you want to apply the same conditional formatting to a range of columns, you could try this:

List the columns you want K:K, or event whole range G:I
Mikey Bronowski 151 Reputation points

2024-02-07T10:39:34.8566667+00:00

One more idea, select all cells, right-click and choose Format Cells. Then on the Protection tab uncheck Locked.

Then from the main menu (Review > Protect > Protect Sheet), make sure Format options are unchecked (no need for a password, but you can have one if you want to be sure no one is changing that setting).

You still should be able to edit data, but when you copy the formatted cell, the format won't be applied to a new cell. It's a workaround, but please have a go :)
Baptiste 25 Reputation points

2024-02-07T10:43:38.84+00:00

Oh thanks I didn't know we can do that !But it might be not easy to use it in my sheet because I have 2 tables I made with and 2 other not formated tables. I have this one mase with the function :

So I want the rules to apply to this table and it is a monthly calendar of what we have to do so we copy/paste our original sheet then we put our exported data in the sheet and we size the table . (it my be another source of our issue).hmmm I saw ur second comment. I'll check this, also it seems that it is my coworkers who mahe the conditionnal formating bug ... I'll try the protect things and I'll tell you if it is(or not) working after a week or two of use. Thanks!
Baptiste 25 Reputation points

2024-02-26T10:08:18.1233333+00:00

Hello, I'm back, So it seems that when my colleague copied/pasted a line in our sheet the conditionnal formating get modified from :

to :

The sheet my colleague copied was locked so I can't tell if uncheck locked would help. I tell you next month ... But it seems to work for the sheet I uncheck locked, so thnak you for that.