Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-03-2012, 06:28 AM
Laurie B. Laurie B. is offline Conditional Formatting. Windows XP Conditional Formatting. Office 2007
Novice
Conditional Formatting.
 
Join Date: Feb 2011
Location: New Jersey
Posts: 7
Laurie B. is on a distinguished road
Default Conditional Formatting.

Good Morning.
I have an Excel spreadsheet that needs to be updated. There could be updates made anywhere on the spreadsheet. I need the cell to change to yellow as modifications are being made. I got as far as copying the entire spreadsheet over to the far right but this is where I am stuck.

I will keep attempting to play until I hear back from someone.


Thank you in advance for your time.
Laurie B.


Reply With Quote
  #2  
Old 04-03-2012, 08:11 AM
JBeaucaire JBeaucaire is offline Conditional Formatting. Windows XP Conditional Formatting. Office 2003
Advanced Beginner
 
Join Date: Dec 2011
Posts: 51
JBeaucaire is on a distinguished road
Default

You're right about copying everything, there HAS to be something to compare to to accomplish this through conditional formatting.

So, let's say everything in column A is duplicated in column AA. As you make edits in column A the values would no longer match and you could highlight the cells via that fact.

So, highlight column A and use this CF setting:

Condition1: Formula Is: =A1<>AA1
Format... Pattern: Yellow
Reply With Quote
  #3  
Old 04-03-2012, 02:45 PM
Laurie B. Laurie B. is offline Conditional Formatting. Windows XP Conditional Formatting. Office 2007
Novice
Conditional Formatting.
 
Join Date: Feb 2011
Location: New Jersey
Posts: 7
Laurie B. is on a distinguished road
Default

I am not sure what I am doing wrong but it isn't working.
I tested a small area
= Q2<>BF2 CF = Yellow
The applies to doesn' look right. =$Q:$Q
Reply With Quote
  #4  
Old 04-03-2012, 03:11 PM
JBeaucaire JBeaucaire is offline Conditional Formatting. Windows XP Conditional Formatting. Office 2003
Advanced Beginner
 
Join Date: Dec 2011
Posts: 51
JBeaucaire is on a distinguished road
Default

Maybe the primary formula needs to be
=Q1 <> BF1

This is pretty basic, you'll have to tinker with it. Should work.
Reply With Quote
  #5  
Old 04-04-2012, 09:50 AM
Laurie B. Laurie B. is offline Conditional Formatting. Windows XP Conditional Formatting. Office 2007
Novice
Conditional Formatting.
 
Join Date: Feb 2011
Location: New Jersey
Posts: 7
Laurie B. is on a distinguished road
Default

Thank you for your efforts. I am beyond tinkering at this point. I am using MS Excel 2010. I have 3 columns with drop down list in each column. If there is a change in any of the cells where the engineer changed grapes to oranges, the cell turns yellow. I am copying those 3 columns over to the right. For this example, I am only using one of the three columns. I highlight the column, click on conditional formating, then selecting "New Rule", then selecting "Use a Formula". This is where I can put =$Q$2:$Q$7 <> $AB$2:$AB$7. As I select the Column and Cells, excel adds the dollars signs.
I then select the formatting and the color. This should be easy, I agree.
I then go to my Column Q line 2 and make a change and nothing happens.

Maybe there's an alternate way to do what I am doing? Honestly I've tinkered for hours trying to get this to work.
Thank you again for all your help.
Laurie B.
Reply With Quote
  #6  
Old 04-06-2012, 06:54 PM
caholmes caholmes is offline Conditional Formatting. Windows Vista Conditional Formatting. Office 2007
Advanced Beginner
 
Join Date: Dec 2008
Location: Sydney, Australia
Posts: 54
caholmes is on a distinguished road
Default

Hi Laurie B,

these can be little tricky to set up now in 2000 & 2010. Follow these steps and it should work.
  1. Select the column that you want to apply the conditional format to.
  2. Select Conditional Formatting from the Home Tab
  3. Select 'New Rule'
  4. Select 'Use a formula to determin which cells to format'
  5. add the following formula: =A1<>AA1 (assuming these are the cells that you are comparing). Remove any $ signs using F4 several times if required.
  6. Click OK
This should now work for all the cells in that column.
Reply With Quote
  #7  
Old 04-09-2012, 05:01 PM
macropod's Avatar
macropod macropod is offline Conditional Formatting. Windows 7 64bit Conditional Formatting. Office 2010 32bit
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

An alternative approach, which doesn't require you to copy the existing data to a new location, is to add a macro like the following to the worksheet's code module:
Code:
Option Explicit
Dim NewVal As String, OldVal As String, NewAddr As String, OldAddr As String
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
NewVal = ActiveCell.Formula
NewAddr = ActiveCell.Address
If OldAddr <> "" Then
  With ActiveSheet.Range(OldAddr)
    If .Formula <> OldVal Then
      .Interior.ColorIndex = 6
    End If
  End With
End If
OldVal = NewVal
OldAddr = NewAddr
End Sub
With this macro, simply changing any cell's contents will change its background colour to yellow.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatting zanat0s Excel 4 01-20-2012 03:30 AM
Conditional Formatting. Conditional formatting with AND, OR Lucky Excel 2 10-03-2011 11:41 PM
Conditional Formatting namedujour Excel 3 08-25-2011 01:46 PM
Conditional Formatting. help with conditional formatting Snvlsfoal Excel 3 07-03-2011 11:55 PM
Conditional Formatting. * Conditional Formatting MS 2010 djreyrey Excel 3 06-03-2011 01:54 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:44 AM.


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