VBA to color matching cells..

[tkaran25]

Hi all,

I want to color cells (dates) in sheet2 if they match with cells in sheet1.
i've used Condtional formating to color using formula =AND(A4<>" ",MATCH(A4,Remarks,0)).
As conditional formating does not actually color cells, i need vba to do so.

i have cross-posted this question below. u can find xls sheet for ref.

Cross-posted here:
http://www.excelforum.com/excel-programming/790045-conditional-formatting.html

Pl help..thanx

 

[Joe4]

As conditional formating does not actually color cells, i need vba to do so.

Sure it does. With Conditional Formatting, you can change the color of the cell text OR the cell background. Just go to the "Fill" tab instead of the "Font" tab under your Formatting options.

 

[MPW]

Here would be a way to match the color of the cells once you conditions are met.

Sheets("Sheet2").Range("A1").Interior.ColorIndex = _
Sheets("Sheet1").Range("A1").Interior.ColorIndex

I think this is what you are asking.

 

[tkaran25]

hello Joe4,

i know CF can fill colors,but i think it just mask the cell not actually change color background.

Pl check this page:
http://www.cpearson.com/Excel/CFColors.htm

*** How can i attach xls sheet in this thread to better describe my question? ***

 

[tkaran25]

Hello MPW,

thanx for that but it does solve my problem

i use this code
Code:

Option Explicit
Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wsCmt As Worksheet
Dim Index As Long
Dim rngCmt1, rngCmt2, rngCmt3 As Range
Set wsCmt = Worksheets("Remarks-Holidays-Leaves")
Set rngCmt1 = wsCmt.Range("Remarks")
Set rngCmt2 = wsCmt.Range("Holidays")
Set rngCmt3 = wsCmt.Range("Leaves")
 
On Error Resume Next
Index = WorksheetFunction.Match(Target, rngCmt1, False)
If Index > 0 Then Target.Interior.ColorIndex = 3
On Error GoTo 0
End Sub

also request to check my cross post.

 

[Joe4]

*** How can i attach xls sheet in this thread to better describe my question? ***

You cannot upload Excel files, but you can post screen images. See here:
http://www.mrexcel.com/forum/showthread.php?t=508133

thanx for that but it does solve my problem

Did you mean to say it "does NOT solve my problem"?
(otherwise, it sounds like we are done!)

i know CF can fill colors,but i think it just mask the cell not actually change color background.

Can you explain exactly why you need to physically color the cells instead of using Conditional Formatting? If your goal is to just have multiple sheets formatted/looking the same, just apply the same Conditional Formatting to both sheets. Note that you can use the Format Painter to copy just your Conditional Formatting.

 

[MPW]

Joe4,

Do you know why I did not have a email generated after tkaran25 and yourself added to the post? An email is usually sent but I did not get one. I only found out because I pulled up my answer and refreshed the tab. Is this a known glitch or what? I was not sure who to ask about this.

Tag your it!

 

[Joe4]

MPW,

Yes, there are currently some database issues affecting the automatic email notifications (see here: http://www.mrexcel.com/forum/showthread.php?t=577749).

Hopefully, they will have it sorted out soon!

 

[tkaran25]

Hi Joe4,

Yes , it did not solve my problem. my mistake....Sorry.

I'm running another code that runs only if the cell is colored and display result in msgbox. I thought cf color's the cell and so i did conditional formatting.

If u have seen my xls file, Sheet named conditional formatting is just for an example. i will delete it later. Actual workbook will contain only 2 sheets. Sheet1 with dates and Sheet2 with data to color.

thanx..

 

[Joe4]

I am unable to download any files (especially those with Macros!) from my current location (network security regulations). So it might be helpful if you could post some images using the tools mentioned in the link I provided back in post #6.

Also, could you describe how your data gets updated?
Do you just need to run this macro once per workbook, or are you constantly going to be making changes to the workbook and need the code to be run dynamically?