Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Closed Thread
 
Thread Tools Display Modes
  #1  
Old 11-23-2020, 04:10 PM
Steve36 Steve36 is offline Unconditional formatting not colouring the cells and not copying across Windows 10 Unconditional formatting not colouring the cells and not copying across Office 2016
Novice
Unconditional formatting not colouring the cells and not copying across
 
Join Date: Nov 2020
Posts: 6
Steve36 is on a distinguished road
Default Unconditional formatting not colouring the cells and not copying across

Please help,

Having problems with my conditional formatting

On Cell F3, I've tried everything and not sure why it does not work.


I want it to change the colours of 11 columns below and then.

I also want to copy the Cell across, but its not allowing me.


For it to work in the F3 column, Change Span, Cat and it will change colour but does not work when I copy it across.

Please help


Attached spreadsheet

Greatful for the help
Attached Files
File Type: xlsx Book1.xlsx (9.6 KB, 9 views)
  #2  
Old 11-23-2020, 11:46 PM
Guessed's Avatar
Guessed Guessed is offline Unconditional formatting not colouring the cells and not copying across Windows 10 Unconditional formatting not colouring the cells and not copying across Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Steve

You said you want this on 11 columns below. I assume you meant 11 rows below.

I also assume you want the cell to be green if it contains Span and it is blue if it contains Cat. To do this, you don't need a countif formula - you can set it to "format only cells that contain" where the cell value is equal to Cat or Span.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
  #3  
Old 11-24-2020, 12:44 AM
Steve36 Steve36 is offline Unconditional formatting not colouring the cells and not copying across Windows 10 Unconditional formatting not colouring the cells and not copying across Office 2016
Novice
Unconditional formatting not colouring the cells and not copying across
 
Join Date: Nov 2020
Posts: 6
Steve36 is on a distinguished road
Default

Hi Tried this,

However it does not work, the cells below do not change colour.

e.g.

Cell Value equal to Cat

However the cells below do not change colour.
  #4  
Old 11-24-2020, 05:19 PM
Steve36 Steve36 is offline Unconditional formatting not colouring the cells and not copying across Windows 10 Unconditional formatting not colouring the cells and not copying across Office 2016
Novice
Unconditional formatting not colouring the cells and not copying across
 
Join Date: Nov 2020
Posts: 6
Steve36 is on a distinguished road
Default Unconditional formatting Copying it across help

Hi Please can anyone help me on this.

See attached spreadsheet

On D339

I select the list and select the subject however, its proving very time consuming to copy the conditional formula across.
I want to select subjects and then it should change color of the below cells.

=COUNTIF(D$339,"Drama")>0

How can I change the formulea so it is easy to copy across to the other cells and the ones below?

Please help
Attached Files
File Type: xlsx September return Staff V4.xlsx (88.0 KB, 10 views)
  #5  
Old 11-24-2020, 11:21 PM
Purfleet Purfleet is offline Unconditional formatting not colouring the cells and not copying across Windows 10 Unconditional formatting not colouring the cells and not copying across Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Not sure what you want to change the colours of, but i have made

Drama in d339 change f341 to e351 Purple

MFL in F339 change f341 to g351 orange
highlight the top left cell of the range (d341) then down to the end cell (e351) so you have all the cells selected of the range you want to change the colour of, then click conditional formatting > New Rule > Use a formula > then in the box type in the formula that can be true or false - =$D$339="Drama" click format and choose the colour you want. Click ok

Then in the Conditional Formatting Rules manager, you should be able to duplicate the rule and amend the Subject and colours.


I think using countifs complicates it a bit. Aldso noticed that some of the headings have a space at the end, which caused some issues......
Attached Files
File Type: xlsx Copy of September return Staff V4_Purfleet.xlsx (62.1 KB, 6 views)
  #6  
Old 11-25-2020, 12:23 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Unconditional formatting not colouring the cells and not copying across Windows 7 64bit Unconditional formatting not colouring the cells and not copying across Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Post # 4 = cross post

Conditional formatting help [SOLVED]


Change a Celll Value & Automatically Change Colour of Cells Below
  #7  
Old 11-25-2020, 12:33 AM
Steve36 Steve36 is offline Unconditional formatting not colouring the cells and not copying across Windows 10 Unconditional formatting not colouring the cells and not copying across Office 2016
Novice
Unconditional formatting not colouring the cells and not copying across
 
Join Date: Nov 2020
Posts: 6
Steve36 is on a distinguished road
Default Unconditional formatting Copying it across help

Hi Purfleet

I opened the spreadsheet and all the conditional formatting is not there.

Tried what you stated and it does not work

the $ prevents its from copying?

Thank you
Attached Files
File Type: xlsx September return Staff V4.xlsx (88.0 KB, 4 views)
  #8  
Old 11-25-2020, 12:37 AM
Steve36 Steve36 is offline Unconditional formatting not colouring the cells and not copying across Windows 10 Unconditional formatting not colouring the cells and not copying across Office 2016
Novice
Unconditional formatting not colouring the cells and not copying across
 
Join Date: Nov 2020
Posts: 6
Steve36 is on a distinguished road
Default

Hi, yes sorry, I had a previous account and as I haven't used it. I made a new one. I will cancel the one with the you have indicated.
  #9  
Old 11-25-2020, 10:11 AM
Purfleet Purfleet is offline Unconditional formatting not colouring the cells and not copying across Windows 10 Unconditional formatting not colouring the cells and not copying across Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I just re-downloaded the sheet from my post and it worked perfectly
  #10  
Old 11-25-2020, 10:54 AM
Steve36 Steve36 is offline Unconditional formatting not colouring the cells and not copying across Windows 10 Unconditional formatting not colouring the cells and not copying across Office 2016
Novice
Unconditional formatting not colouring the cells and not copying across
 
Join Date: Nov 2020
Posts: 6
Steve36 is on a distinguished road
Default

Hi Purfleet

Yes it does work, however if I copy this to the other cells, that is were the problem is.

If I move absolute cell reference, it will stop working. Can I do anything to the formulae to allow it to be easy copied to the cells with manually changing the cells?

Thank you
  #11  
Old 11-26-2020, 06:47 PM
macropod's Avatar
macropod macropod is offline Unconditional formatting not colouring the cells and not copying across Windows 10 Unconditional formatting not colouring the cells and not copying across Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Now also cross-posted at: Unconditional formatting not colouring the cells and not copying across | MrExcel Message Board
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters
I note you've already been pinged elsewhere for cross-posting without providing links. Keep it up and you'll find no-one will help you and your accounts on the forums whose rules you're violating may even be terminated.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Closed Thread

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Word 2016 mac text not colouring propperly GAMIE64 Word 1 04-29-2016 06:18 AM
Unconditional formatting not colouring the cells and not copying across Copying Same Value Between Two Cells rsrasc Excel Programming 3 02-29-2016 10:53 PM
Unconditional formatting not colouring the cells and not copying across Copying text into multiple cells at once BIMwit Word Tables 1 05-14-2015 09:50 PM
Copying text range of cells to different cells adds an extra line jpb103 Word VBA 2 07-23-2014 12:22 PM
Unconditional formatting not colouring the cells and not copying across Copying cells isminoh Excel 5 04-18-2012 11:49 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:17 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft