Conditional formating columns

[Jeffbond]

I am trying to create a basic programme of works,

I am tring to format a full column based on the text of a cell. so if the cell has "M" then format border LHS,

I initially had borders drawn in as Sample A, but when i highlight and drag the coloured cells C10-F10 over to the right to allow for reprogrammed work it obviously drags the border with it. as sample B.

If the 'border format was done by conditional formatting the column, then i could drag the coloured cells accross and the vertical lines would stay in the correct place. I know this would be easier to just use the borders, but this is an excerpt of a programme with 100's rows & columns.

Thanks in advance
Jeff

[etaf]

i have used a new example in your spreadsheet

Select the range you want
C1 to ???
then use a formula

=C$4="M"

I have set up in AA and USED AC


for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
C2:Z100 - extend the rows/columns to meet your data range of rows & columns

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=C$4="M"

Format [Number, Font, Border, Fill] - Border - Left line
choose the format you would like to apply when the condition is true
OK >> OK

[Jeffbond]

Hi Etaf,

Thanks for speedy reply, the folmula did work, however when i grab cells AL13-AQ13 etc to move forward it then takes the border edge with it, which then ends up in the middle of my block.

In a way i need the above formula to lock even though i drag cells either side of it accross, so the left hand of column "M" always keeps the border line.

Jeff.

[etaf]

if you drag , it will also change the range for conditional formatting
I guess it may need VBA , not sure , but I dont know how to keep it from changing formulas etc when dragging cells

Why are you dragging cells ?

you could have a start and stop date to create the colour Gantt chart style

[Jeffbond]

Dragging cells because my boss cant use MS project or Asta..... His programmes are done by colouring blocks in excel, i need to use these to work from and update him with ongoing progress, obviously a delay would push everything to the right, and when i tried to move everything that is when i discovered the borger edges move as well. I am trying to makle it easier with what i have.

[etaf]

sorry,

other than perhaps using VBA, which i do not do now, as last used in 90's early 2000 - so not upto date
I dont know how you can stop the formatting from a simple drag operation

[chronjy]

try this

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Jeffbond]

thanks for help both, this works great.