COLOR OF CELL OF A COL WHEN CHANGED THEN CELL COLOR OF B ALSO CHANGED

[AFZAL SOHAIL]

Hello Respected Team:-
I have a question that if I change the color of Cell in col-A then color of adjacent col-b cell should changed is as under:-
I need a formula.

FZRD: OLT-1 to C-10 Main Chungi Amrsadhu	TNSP
SMD: B-36 Opp. Shadman Market to B-1 Bagh Gul Begum	SMD
SMD: B-25 Cricket House to C-5 Services Hospital	SMD
8057-FZRD (11/0/3) to 7301-Ufone (2/0/2)	TNSP
MLRD: OLT-1 to C-41 Sabzazar	SMD
MAL: MSAG-13 Sundar Industrial Estate to MSAG-15 Sundar Industrial Estate	JTN
8062-WTN to 7038-Ufone	TNSP
GTN: C-2 P-Block GLB-III to C-2A Main Enterance GOR-VI Opp. Arfa Karim Tower	GTN
JTN --- BTN	JTN
FZRD --- TNSP	TNSP
GTN: OLT-2 to 94-S Block Model Town	GTN

 

[Peter_SSs]

Please review the Forum Rules, particularly #14 regarding the use of all upper case in your thread titles. Thanks.

 

[AFZAL SOHAIL]

Ok Sir.
I am sorry for this, I do not know to break the rule, in future I will be careful.

 

[Peter_SSs]

Cheers. Thanks.

 

[AFZAL SOHAIL]

Dear Team:
Kindly help me

 

[Peter_SSs]

How are the colours being changed in column A?

1. Manually
2. Conditional Formatting
3. VBA code

 

[AFZAL SOHAIL]

Dear Sir,
Manually

 

[Peter_SSs]

Dear Sir,
Manually

Well, there is no 'event' for excel to capture when you manually colour a cell. The best I can come up with is a way to replicate the colours from Col A into Col B but only when the selection is changed. So if you select A8 and colour it red, B8 will not turn red until your select some cell other than A8. See if it is any use to you.
Note that this also copies any other formatting as well (eg Bold font, cell borders etc)

To implement my idea..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by selecting any cell (or cells) in Col A and apply a colour (or remove an existing colour) then move to some other cell.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim s As Range, ac As Range

  Set s = Selection
  Set ac = ActiveCell
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  With Intersect(ActiveSheet.UsedRange, Columns("A"))
    .Copy
    .Offset(, 1).PasteSpecial Paste:=xlPasteFormats
  End With
  Application.CutCopyMode = False
  s.Select
  ac.Activate
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

 

[AFZAL SOHAIL]

Dear sir,
These codes are working very well as i want.
If possible to make a formula, because these type of report to sent my department and I want to sent without any vba code

 

[Peter_SSs]

It is not possible for a formula to detect when colour is applied to another cell.