none
Copying Formatting to adjacent cell using VBA RRS feed

  • Question

  • Hi,

    Summary below if you don't want to read all this.

    I am an semi-experienced excel user but I do not have any experience when it comes to using VBA.

    Basically I have a spread sheet that is used for prioritising problems a company faces. 

    I've set up all the formulae to assign each problem a 'score' based on a severity and the cost associated with fixing it. It outputs a numerical Score which is very easy to conditionally format using a colour scale each problem is a row and the score is a single column, in this case column 'k'

    If the user enters an erroneous value (say text into the cost cell) the formulae will return errors like "enter cost" or "check cost" the cells are conditionally formatted based on the text that appears in the cell (cell goes blue if "check cost" etc.) 

    Next to the 'score' column (column 'L') I have an action column with lookups an 'action' based on the score, the actions are text values like "fix immediately" "Plan solution" etc. It also return the same error values with the conditional formatting for those.

    However, I cant find a way for the cells in my 'action' column to match the colour of the 'score' cell which ranges from green to red like a stop light with many shades in between.

    I could set discrete value ranges for the colouring which would work but the changing colour works well at identifying which problems are more important.

    IN SUMMARY, skip here if you don't want to read above.

    Basically I want to make the formatting of each cell in column 'L' match the formatting of the adjacent cell in column 'K' where each cell row-by-row might have differing fill colours from the colour scale applied to column 'k'.

    Can someone please tell me the VBA script to do that?

    I will try and work it out on my own with google, but I have no experience.

    Thanks,

    Tuesday, February 2, 2016 2:53 AM

Answers

  • Thanks for the input guys, sorry for the delay, basically I've been unable to post photos because my account wasn't "confirmed" up until now.

    Here is a rudimentary image of what I want to do.

    column K has a colour range applied to it based on the numbers. I would like column L to also have the colour range applied to it.

    Basically I got around this by using the conditional formatting in a different way. Rather than having a separate column for K and L I applied conditional formatting to K in addition to the colour scale. I made 5 rules of conditional formatting like if 0<k1<1 etc. and applied a custom format for each rule so that it would display the number and then the action (column L) both in column K. This removes the need for column L. So for example for score=0 format is #" - Just do it"

    This isn't too much work for this scenario, however if you had more 'actions' you would need more rules which could get time consuming.

    thanks again for the help


    Everyday I'm Struggling, Struggling




    Wednesday, February 24, 2016 7:08 AM

All replies

  • Hi,

    How about this?
      

    Code:
      
    Private Sub CommandButton1_Click()
        Range("K1:K16").Copy
        Range("L1:L16").PasteSpecial (xlPasteFormats)
        Application.SendKeys "{ESC}"
    End Sub
    

    Regards.
    Tuesday, February 2, 2016 3:28 AM
  • Hi Ashidacchi,

    Thanks for the response, unfortunately the code only copies over the formatting conditions, which does include the colour scale but it does not apply it to the cells containing the text as I would like it to.

    IE if I manually enter a number into the cells in column L it colours it accordingly but not if text is present.

    I have manage to get around the problem however, because there were only 5 'actions' but a larger range of 'scores' I have managed to use conditional custom formats to get numbers corresponding to each value to display as the score and the action in the same cell, where the action is only a format much like adding $ or something, thus excel reads the numerical value and not the text, and colours the cell accordingly

    thanks again

    Tuesday, February 2, 2016 7:08 AM
  • Hi MayorOfStruggleTown,

    I'm poor at English (I'm a Japanese). So, it's hard for me to understand your need.
    I'll be very happy, if you would provide some pictures that explain your want. 

    Regards.
    Tuesday, February 2, 2016 7:17 AM
  • Hi May,

    I agree with Ashidacchi, it would be helpful if you could share us a screen shot or simple excel about your requirement.

    Could you achieve your requirement with copying manually? If you could, I suggest you record your steps and modify it to meet your requirement. Setps:Develop tab->Code->Record Macro-> manually copy-> Stop Macro->ALT+F11 to check recorded code.

    For information about Range.PasteSpecial, you could refer the link below:
    # Range.PasteSpecial
    https://msdn.microsoft.com/en-us/library/office/ff839476.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, February 3, 2016 5:19 AM
  • Thanks for the input guys, sorry for the delay, basically I've been unable to post photos because my account wasn't "confirmed" up until now.

    Here is a rudimentary image of what I want to do.

    column K has a colour range applied to it based on the numbers. I would like column L to also have the colour range applied to it.

    Basically I got around this by using the conditional formatting in a different way. Rather than having a separate column for K and L I applied conditional formatting to K in addition to the colour scale. I made 5 rules of conditional formatting like if 0<k1<1 etc. and applied a custom format for each rule so that it would display the number and then the action (column L) both in column K. This removes the need for column L. So for example for score=0 format is #" - Just do it"

    This isn't too much work for this scenario, however if you had more 'actions' you would need more rules which could get time consuming.

    thanks again for the help


    Everyday I'm Struggling, Struggling




    Wednesday, February 24, 2016 7:08 AM
  • Hi,

    Would you explain again what you want to do with VBA?

    I suppose if value of cell K1 = 16, then color of cell L1 should be Red and its value should be "Just Do it", if K3 = 9 then L3 is color Yellow and value is "Plan"?(row number 1 and 3 is an example, any row number will do) 

    Regards.


    • Edited by Ashidacchi Wednesday, February 24, 2016 7:28 AM
    Wednesday, February 24, 2016 7:19 AM
  • So the score ranges are as such:

    0 - just do it

    1 to 3 - execute

    4 to 8 - plan

    9 to 15 - consider

    16 - don't do

    So there aren't 5 discrete colours but a range green to red from 0 to 16

    And I would want to colour the action cell with the exact same shade as the score column. But the actions are discrete which is how I applied them as 5 conditional formats with a custom text formatting rather than a fill colour.


    Everyday I'm Struggling, Struggling

    Wednesday, February 24, 2016 7:32 AM
  • You marked as answer on your post. Has your question resoleved?
    Wednesday, February 24, 2016 7:36 AM
  • Kind of simple but i hope it works!

    Sub newmacro() Dim count As Variant Range("k2").Select If ActiveCell.Offset(1, 0).Value = "" Then

    ActiveCell.Copy
    ActiveCell.Offset(0, 1).PasteSpecial xlPasteFormats
    ActiveCell.Offset(1, -1).Select

    Else count = Range(Selection, Selection.End(xlDown)).count Do While count <> 0 ActiveCell.Copy ActiveCell.Offset(0, 1).PasteSpecial xlPasteFormats ActiveCell.Offset(1, -1).Select count = count - 1 Loop End If End Sub

    Kind Regards,

    Soliddrew!

    Happy Excelling!


    • Proposed as answer by Soliddrew Wednesday, February 24, 2016 8:48 AM
    • Edited by Soliddrew Wednesday, February 24, 2016 8:50 AM
    Wednesday, February 24, 2016 8:48 AM
  • I did solve my own problem yes, but not in the way I thought I would. Rather than formatting the column containing the action with a colour, I formatted the coloured column with the number to add the action.

    Everyday I'm Struggling, Struggling

    Thursday, February 25, 2016 2:31 AM