INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...Your site is one of the cleanest and BEST forums that I have seen. I have sent quite a few people your way. Keep up the good work!!!"

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > Languages > VBA Visual Basic for Applications (Microsoft) Forum

Conditional Format Cell Fill using VBA

CODE

Sub AbstractData() Dim r As Range, wsAdd As Worksheet, t As Range, rSEQ_NO As Range, mypassword As String, ws As Worksheet With Sheets("RawData_A") Set rSEQ_NO = .Rows(1).Find("SEQ_NO") If Not rSEQ_NO Is Nothing Then For Each r In .Range(.[A2], .[A2].End(xlDown)) Sheets("Template").Copy After:=Sheets(Sheets.Count) Set wsAdd = ActiveSheet wsAdd.Name = .Cells(r.Row, rSEQ_NO.Column).Value wsAdd.Tab _ .Color = 49407 For Each t In [From] .Range(.Cells(r.Row, t.Value), .Cells(r.Row, t.Offset(0, 1).Value)).Copy wsAdd.Range(t.Offset(0, 2).Value).PasteSpecial _ Paste:=xlPasteAll, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False wsAdd.Range("A5.J87").HorizontalAlignment = xlLeft Next Next End If End With End Sub

When I record a macro to show the comparison and changing of cell colour it is:

CODE

Sub format_test() Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$J$14<>$B$14" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 49407 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False End Sub

I'm assuming the function will have to be built into the template so it copies to all the worksheets but not sure. Any assistance greatly appreciated.

SkipVought (Programmer)

16 May 12 21:08

Shelby,

Why would you use VBA? Why not use the native Conditional Formatting feature?

With Excel 2007+, if your Table is defined as a Structured Table (Data > Tables > Table), the CF range can be related to the table column ranges as they change.

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

16 May 12 21:35

Hi Skip

I don't want to use conditional formatting because the workbook may be required in different versions of Excel and I know there could be problems because conditional formatting is different between them. I believe 2007 and 2010 are similar but 2003 isn't.

Thanks.

SkipVought (Programmer)

16 May 12 22:10

What is the Selection? You need to EXPLICITLY define the range to be selected, within the procedure.

The cell references should NOT be absolute, rather relative...

CODE

Sub format_test() Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=J14<>B14" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 49407 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False End Sub

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

SkipVought (Programmer)

16 May 12 22:23

BTW, you ARE using Excel 2007+ code!!!

I doubt that this code will run in 97-2003.

THIS is a strictly VBA solution for rows 14:16...

CODE

Sub CF_Text() Dim r As Range For Each r In Range("B14:B16") If r.Value <> Cells(r.Row, "J").Value Then Intersect(r.EntireRow, r.Parent.UsedRange).Interior.Color = 49407 Else Intersect(r.EntireRow, r.Parent.UsedRange).Interior.Color = vbWhite End If Next End Sub

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

SkipVought (Programmer)

16 May 12 22:29

This is a bit more efficient...

CODE

Sub CF_Text() Dim r As Range, rng As Range Set rng = ActiveSheet.UsedRange For Each r In Range("B14:B16") With Intersect(r.EntireRow, rng) If r.Value <> Cells(r.Row, "J").Value Then .Interior.Color = 49407 Else .Interior.Color = vbWhite End If End With Next Set rng = nothing End Sub

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

SkipVought (Programmer)

16 May 12 22:47

Quote:
I wish to use VBA to conditionally format a cell in Excel 2010.

Quote:
I don't want to use conditional formatting because the workbook may be required in different versions of Excel and I know there could be problems because conditional formatting is different between them. I believe 2007 and 2010 are similar but 2003 isn't.

Then WHY are you designing & coding in 2010? Your application is likely to fail in earlier versions.

You could design in Excel 2003 and run other version in Compatabliity mode.

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

17 May 12 10:54

Hi Skip

Thanks very much...but it doesn't work.

I assumed I was to insert the code in the worksheet of Template so it would be copied to all abstracts when AbstractData() was run.

So I went to one of the abstracts, changed the values in J14 to not match B14 and nothing happened to J14.

What am I doing wrong?

SkipVought (Programmer)

17 May 12 11:09

It appears that you...

1. COPY the Template SHEET within the workbook

2. COPY some CELLS and PASTE them into the new template copy.

IF the range that you paste into, is the same range that contains the Conditional FORMAT, (do you see what's coming?) the Conditional FORMAT is CHANGED, just as any other cell would CHANGE FORMAT is you pasted something into it.

You could PASTE SPECIAL and only past the VALUES, if that is indeed the case.

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

17 May 12 11:21

Hi Skip

I guess I didn't explain the process well.

With AbstractData() it is copying the same data into cells B5 to B38 as it is in J5 to J38. This is being used to review medical records so if the reviewer sees an error in the original data then they will change it in the J columns. So if they change it to not equal B anymore then I want it to be highlighted.

I was looking into the change by val but I want the highlight to occur if it doesn't match and not that it was changed (because if they change it back to match, it would still be highlighted if using the change function which I don't want).

Thanks.

SkipVought (Programmer)

17 May 12 11:58

Is your program or is the user, EITHER ONE, pasting ANYTHING into column B or column J?

If so, that paste process DESTROYS the CF!!!

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

SkipVought (Programmer)

17 May 12 12:30

Quote:
because if they change it back to match, it would still be highlighted if using the change function which I don't want

NOT TRUE!!!

Here's how I would opt to do to using the Worksheet_Change event...

CODE

Private Sub Worksheet_Change(ByVal Target As Range) Dim t As Range, rng As Range Set rng = ActiveSheet.UsedRange For Each t In Target With Intersect(t.EntireRow, rng) 'is change in column B? If Not Intersect(t.EntireRow, rng, Cells(1, "B").EntireColumn) Is Nothing Then If t.Value <> Cells(t.Row, "J").Value Then 'shade this row .Interior.Color = 49407 Else .Interior.Color = vbWhite End If End If 'is change in column J? If Not Intersect(t.EntireRow, rng, Cells(1, "J").EntireColumn) Is Nothing Then If t.Value <> Cells(t.Row, "B").Value Then .Interior.Color = 49407 Else .Interior.Color = vbWhite End If End If End With Next Set rng = Nothing End Sub

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

17 May 12 17:22

Hi Skip

Thanks.

Cells B5 to B38 are the exact same as J5 to J38 to start. Cells B41 to B80 have data in them but not in column J because the reviewer is going to code the chart themselves and enter data in that column as they go.

The template is formatted with dividing lines which are wiped out by the current code making it white if matching. Taking the else code out to leave alone if they match works for the cells B5 to B38 but when data is entered in column J, it doesn't then change to be white due to the change in code.

Also, I would just like cell to have the colour and not the entire row. I see this has "entire row" and "entire column" but how can I limit only to certain rows/cells/columns?

Thanks.

SkipVought (Programmer)

17 May 12 17:31

Quote:
how can I limit only to certain rows/cells/columns?

Which certain? I need a specification.

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

SkipVought (Programmer)

17 May 12 17:41

If it is ONLY the cell that changed that you want shaded...

CODE

For Each t In Target ~~With Intersect(t.EntireRow, rng)~~ With t

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

SkipVought (Programmer)

17 May 12 17:50

Sorry, I failed to address the WHITE cell issue...

CODE

Private Sub Worksheet_Change(ByVal Target As Range) Dim t As Range, rng As Range Set rng = ActiveSheet.UsedRange For Each t In Target With t 'is change in column B? If Not Intersect(t.EntireRow, rng, Cells(1, "B").EntireColumn) Is Nothing Then If t.Value <> Cells(t.Row, "J").Value Then 'shade this row .Interior.Color = 49407 Else .Interior.ColorIndex = xlColorIndexNone End If End If 'is change in column J? If Not Intersect(t.EntireRow, rng, Cells(1, "J").EntireColumn) Is Nothing Then If t.Value <> Cells(t.Row, "B").Value Then .Interior.Color = 49407 Else .Interior.ColorIndex = xlColorIndexNone End If End If End With Next Set rng = Nothing End Sub

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

17 May 12 17:53

Hi Skip

Thanks very much, that fixes the B5 to B38.

However, as described, B41 to B80 have data in them from the onset because of the AbstractData() macro. They show as yellow (49407) at the start which is fine because J column is blank and therefore doesn't match. But if I enter in the exact codes as in column B41 to J41, then J41 also turns yellow.

I actually took out the part of the code for "is change in column B" because column B will always be right (or at least be the original data) and J is to compare to B.

So how to get it so that either B changes back to white if J is the same OR have B starting off as white and then only if J is different should J be highlighted?

Thanks.

shelby55 (TechnicalUser)

17 May 12 17:54

P.S.
If this can't be done then if we can just control the range so that instead of all rows it's only rows 5 to 38 then that would be great. Thanks.

SkipVought (Programmer)

17 May 12 17:58

If the code for column B changing is commented out to begin with, before AbstractData() runs, would that not solve that issue?

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

17 May 12 18:10

Hi

Nope...once copied the cells from B41 to B80 are yellow and don't change to white even if entries in J match them.

SkipVought (Programmer)

17 May 12 18:12

How are the column B cells turning yellow, when that code has been removed?

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

17 May 12 18:22

Hi

I don't know. Below is the code:

CODE

Private Sub Worksheet_Change(ByVal Target As Range) Dim t As Range, rng As Range Set rng = ActiveSheet.UsedRange For Each t In Target With t 'is change in column J? If Not Intersect(t.EntireRow, rng, Cells(1, "J").EntireColumn) Is Nothing Then If t.Value <> Cells(t.Row, "B").Value Then .Interior.Color = 49407 Else .Interior.ColorIndex = xlColorIndexNone End If End If End With Next Set rng = Nothing End Sub

SkipVought (Programmer)

17 May 12 18:27

My column B changes NOT.

I cannot understand that if you BEGIN the process; copy the template; run AbstractData; make change to the new sheet in column J or even B for that matter.

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

17 May 12 18:37

Hi Skip

I don't copy the template and run AbstractData, AbstractData runs copying the template to create a worksheet per entry in the worksheet of "raw data".

AbstractData() is at the top of the post...should this code be incorporated into that somehow?

SkipVought (Programmer)

17 May 12 20:16

THAT is the process that I am referring to: the process of INITIALIZING your sheet.

If the user happens to change the shading on the sheet, do you want some code to change that shading? There is no event that 'recognizes' such changes -- only changes in VALUES. Of course, you could write some fancy code to run on the Worksheet_SelectionChange event, but that might be a daunting task for you.

The posted code will change the shading in column J ONLY!!!

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

18 May 12 10:51

Hi Skip

So then how do I limit this to only be for Rows 5 to 38?

Thanks.

SkipVought (Programmer)

18 May 12 11:01

Change the Set rng statement...

CODE

Set rng = Intersect(ActiveSheet.UsedRange, rows("5:38"))

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

18 May 12 11:46

Thanks Skip!!

shelby55 (TechnicalUser)

21 May 12 14:45

Hi

Sorry to be a pain but how do I limit to the columns of A to J?

I tried:

CODE

Set rng = Intersect(ActiveSheet.UsedRange, Rows("5:38"), Columns("B:J"))

but it still changes to orange if entry in column R.

Thanks.

SkipVought (Programmer)

21 May 12 15:08

Well you removed rng from the intersect, thus causing the ENTIRE USED RANGE to be included!!!

So you want rows 5:38 and B:J to be the area within shading CAN take place and specifically when the column J value <> the column B value, then B:J for that row would be shaded?

CODE

Private Sub Worksheet_Change(ByVal Target As Range) Dim t As Range, rng As Range Set rng = Intersect(Rows("5:38"), Range([b1], [j1]).EntireColumn) For Each t In Target With Intersect(t.EntireRow, rng) 'is change in column J? If Not Intersect(t, rng, Cells(1, "J").EntireColumn) Is Nothing Then If t.Value <> Cells(t.Row, "B").Value Then .Interior.Color = 49407 Else .Interior.ColorIndex = xlColorIndexNone End If End If End With Next Set rng = Nothing End Sub

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

21 May 12 15:42

Hi Skip

Thanks but I don't want the entire row to be yellow, only the cell in J. Is that possible?

SkipVought (Programmer)

21 May 12 15:48

then explain "how do I limit to the columns of A to J?"

What does that mean, rather than limit ot column J"

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

21 May 12 15:52

Figured it out from your other comments: change to only be "with t" and not the range. Thanks Skip.

There are still issues if the user changes the selection and then changes it back to be the same as column B. I realize that is because of the code. You indicated in one of your posts

Quote (Skip)
Of course, you could write some fancy code to run on the Worksheet_SelectionChange event, but that might be a daunting task for you.

Can you point me in the direction of the concepts behind the "fancy code"? Daunting or not I would like to see if I can make it happen. Thanks.

SkipVought (Programmer)

21 May 12 16:02

First write a detail specification discribing what should happen when changes are made in the various areas of the sheet. Include what adds the shading and what removes the shading.

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

21 May 12 16:50

Hi Skip

The cells in J2 to J38 need to match the value of the corresponding cells in B2 to B38..so J2 needs to match B2, J3 needs to match B3 etc. and if not, then the cells in J needs to change colour.

As I mentioned, they will always match on initialization because the data was copied from the same source to both columns. So if the user changes the data in J to not match B then the cell in J needs to change to be yellow. If the user then changes the value in J back to match the value in B then it needs to be with white fill (not nothing, white).

Is that enough detail? Thanks.

SkipVought (Programmer)

21 May 12 21:57

Isn't that what the latest published code does, with the exception of White, which is .Interior.Color = vbWhite as originally posted.

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

shelby55 (TechnicalUser)

23 May 12 10:50

No, Skip, it doesn't. If I change J6 to not match B6 then it turns yellow but if I change back so it matches B6 then it doesn't turn back to white because the code is only on change not on matching.

SkipVought (Programmer)

23 May 12 11:07

Quote:
because the code is only on change not on matching

NOT TRUE!!!

CODE

'is change in column J? If Not Intersect(t, rng, Cells(1, "J").EntireColumn) Is Nothing Then If t.Value <> Cells(t.Row, "B").Value Then .Interior.Color = 49407 Else .Interior.ColorIndex = xlColorIndexNone End If End If End With

If you want WHITE then, as advised previously...

CODE

Else .Interior.Color = vbwhite End If

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To VBA Visual Basic for Applications (Microsoft)

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

Conditional Format Cell Fill using VBA

CODE

CODE

CODE

CODE

CODE

Quote:

Quote:

Quote:

CODE

Quote:

CODE

CODE

CODE

CODE

CODE

CODE

Quote (Skip)

Quote:

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!