• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

color cell under condition

A

asafraz81

Member
hi,

i wrote a code to check cells in range. if the cells not match then color it in yellow.

i need help just to ad to the code the order to color the code. here is the code:

Sub checkcells()

Dim cell As Range

Dim x As Range

For Each cell In Range("a1:a2")

For Each x In Range("c1:c2")

If cell <> x Then

__________

End If

Next x

Next cell

End Sub
 
i dont know why, but when i run the macro it giving me eror "subscript out of range"

i put numbers in cells a1,a2 and trying to compare to cells in c1,c2

and its not working. i know it should be very simple code.

please help me i need to run this code on 30 different ranges.
 
Hi,


Before we tackle the runtime error, I think it's more important to have a look at the logic of your loops because I think there's a good chance that they're not doing what you want them to.


Let me ask you this question...

When you are comparing A1:A2 to C1:C2, let's consider how A1 is compared. Should:

(a) A1 be highlighted yellow if it does not equal C1 (the value of C2 does not matter because A2 will be compared to C2)

(b) A1 be highlighted if it does not equal EITHER C1 or C2

(c) A1 be highlighted if it does not equal BOTH C1 and C2


Which scenario do you want? Currently your code is trying to do (c).


Either way, I'm not sure why you're trying to do this with VBA when conditional formatting should be able to do this fairly easily?


Okay, now to explain the error. Since you're using the vbYellow constant you should use the Interior.Color property rather than the Interior.ColorIndex property.


Hope that helps...

Colin
 
@Colin Legg

Thanks for the catch, vbYellow is 65535 which is suitable for Color but ColorIndex requires a 6.

Regards!
 
hi colin.

i want that cell a1 will compare to c1, and a2 compare to c2.

the reason why i'm not doing it in conditional formating is the fact that i need to build this code on 30 different ranges scatered on a file with 6 sheets.

any way' now the code isnt working no matter what numbers i put inside it always coloring the cell like it ignores the if condition.

i have to finish the code until saturday please someone help me correct it...
 
Hi,

If you want to compare two columns (A & C). First select range of column "C" then select range of column "A", now click F5 for Go To Special and click on Row differences, press OK button and click on fill color.
 
Hi,


If you refer to my previous post where I listed the 3 scenarios, you want your code to do scenario (a). At the moment your code is doing scenario (c). You need to think about how you would write the code to do (a) instead. Have a go and if you have problems then post what you've done and I'll try to help. :)
 
@asafraz81


Hi


Try This hope it will help full to you


Sub Macro1()

'

' Macro1 Macro

'

' Keyboard Shortcut: Ctrl+q

'

Dim cell As Range

Dim x As Range

For Each cell In Range("a1:c2")

For Each x In Range("C1:C2")

If cell = x Then

cell.Interior.Color = vbYellow

If cell = "" Then

cell.Interior.ColorIndex = vbNormal

End If

End If

Next x

Next cell

End Sub


If it is not help full then please try to upload a sample work book


Thanks


SP
 
i try it.

its not working, and i understand why.

the last rows its the problem.

after the second "end if" there is "next x" that run the loop again until he finish the x range.

after finishing x range the macro goes to cell range.

well, this code is not good and not working.

someone can help to find solution to this problem???
 
See if this makes sense:

[pre]
Code: 
Sub FooBar()

Dim rngToCheck As Range
Dim rngTopLeftCompareTo As Range
Dim rngCell As Range
Dim lngRowOffset As Long
Dim lngColOffset As Long

'these are the cells which will be highlighted
Set rngToCheck = Range("A1:A2")

'this is the top left cell in the range we're going
'to compare against
Set rngTopLeftCompareTo = Range("C1")

'work out the relative position of the two ranges
lngRowOffset = rngTopLeftCompareTo.Row - rngToCheck.Cells(1).Row
lngColOffset = rngTopLeftCompareTo.Column - rngToCheck.Cells(1).Column

'highlight any differences in the range we're checking
For Each rngCell In rngToCheck.Cells
If rngCell.Value <> rngCell.Offset(lngRowOffset, lngColOffset).Value Then
rngCell.Interior.Color = vbYellow
End If
Next rngCell

End Sub
[/pre]
 
If the two ranges are on different sheets then slightly different code is required.

[pre]
Code: 
Sub FooBar2()

Dim rngToCheck As Range
Dim rngTopLeftCompareTo As Range
Dim r As Long, c As Long

'these are the cells which will be highlighted
Set rngToCheck = Worksheets("Sheet1").Range("A1:A2")

'this is the top left cell in the range we're going
'to compare against
Set rngTopLeftCompareTo = Worksheets("Sheet2").Range("C1")

'highlight any differences in the range we're checking
For r = 1 To rngToCheck.Rows.Count
For c = 1 To rngToCheck.Columns.Count
If rngToCheck.Cells(r, c).Value <> rngTopLeftCompareTo.Cells(r, c).Value Then
rngToCheck.Cells(r, c).Interior.Color = vbYellow
End If
Next c
Next r

End Sub
[/pre]

Just change the sheet names and range address as you require.


If you point out which part of the code you're having trouble understanding then I'll be happy to explain it, but hopefully the comments in the code should give you some help?
 
You must log in or register to reply here.
Back
Top